powerbi

Power BI - Showing all Rows on a Filtered Visual


I am trying to build a visual to show payment progress for contracts but am having some difficulty having all the items show up in my visual when I use a slicer to filter the data. I am trying to be able to slice previous payments to have a historical view of payment progress.

In my data table not even line appears on every payment and it doesn't get recorded as a 0 either so when I filter to a payment the visual will only display the rows that have that associated payment number in my data table.

The pbix file can be found here: https://www.dropbox.com/scl/fi/3wpe3w85fxvjsefhphs4p/Contract-Data.pbix?rlkey=6qbi25w0xge8wh6bl2i5wmvsk&st=clyp5prn&dl=0

I have tried to create a measure that ignores the filter, creating a measure to look at all rows and pull the last known value if it is not in the selected payment in the slicer, selecting the show values with no data, and creating a seperate table with all line guids.

Current Visual

My desired output

The raw data I am using:

Contract Line Number Line GUID Pay Amount Payment Number
1 1 AAA1 10 1
1 1 AAA1 500 2
1 1 AAA1 1000 5
1 2 BBB2 500 4
1 3 CCC3 500 2
1 3 CCC3 100 3
1 3 CCC3 100 7
1 4 DDD4 100 6
1 5 EEE5 500 3
2 1 111A 100 1
2 1 111A 500 3
2 2 222B 1000 3
2 2 222B 500 3
2 3 333C 500 2
2 3 333C 1000 3

Solution

  • you can create two tables for selection

    contract = DISTINCT('Table'[Contract])
    paymentnumber = DISTINCT('Table'[Payment Number])
    

    Do not create relationships between tables.

    Then create mesaures

    MEASURE =
    CALCULATE (
        SUM ( 'Table'[Pay Amount] ),
        FILTER (
            'Table',
            'Table'[Contract] = MAX ( contract[Contract] )
                && 'Table'[Payment Number] = MAX ( paymentnumber[Payment Number] )
        )
    ) + 0
    
    MEASURE 2=
    CALCULATE (
        SUM ( 'Table'[Pay Amount] ),
        FILTER (
            'Table',
            'Table'[Contract] = MAX ( 'contract'[Contract] )
                && 'Table'[Payment Number] < MAX ( paymentnumber[Payment Number] )
        )
    ) + 0
    

    enter image description here