Cost Status filter in different columns

[hanolby]

Hi, everyone, whats up?

So i'm trying to create a report in which i am able to filter the installments of a payment by its planned date and its status.

for the date part, since the date is in different columns, i've created a master calendar table and have activated different relationships so i can filter by year (with calculate and userelationship);

But for the status part, i cant seem to find a solution. since the status are displayed in different columns, how can i filter them?

ps: unfortunately, putting all payments and dates in one single "payment" column and "date" column, isnt an option, considering that the database is too big.

I've attached and image of what the database looks like.

for example, how could i create a report in pbi showing how much payment of product C and D is pending in 2024 and 2025?

thanks in advance! 

[hanolby]

Hello, Yolo Zhu, in this case, I've provided this example with products C an D, but the best scenario would be if possible, to get a matrix by filtering in a list filter (pending, or ok, or any other status I might have for a certain payment), would that be posible?

since the status are displayed in different columns, I wasn't sure how I could drag it to a table visual and make it filter all payments, in other words, the table matrix I would expect getting would be something like in the images, but in a dinamic table (resumed) way so product arent duplicate or triplicate...

resumed way

[Anonymous]

Hi @hanolby 

You can create a new table in power bi 

Table 3 = var a=UNION(SUMMARIZE('Table','Table'[1st status]),SUMMARIZE('Table','Table'[2nd status]),SUMMARIZE('Table',[3rd  status]))
return SUMMARIZE(a,[1st status])

Then I have changed the measure

Measure = var _1st=SUMMARIZE(ALL('Table'),[Product],'Table'[1st date],'Table'[1st status],'Table'[first payment])
var _2nd=SUMMARIZE(ALL('Table'),[Product],'Table'[2nd date],'Table'[2nd status],'Table'[2nd payment])
var _3rd=SUMMARIZE(ALL('Table'),[Product],'Table'[3rd date],'Table'[3rd  status],'Table'[3rd payment])
var _uniontable=UNION(_1st,_2nd,_3rd)
return CALCULATE(SUMX(FILTER(_uniontable,[Product] in VALUES('Table'[Product])&&YEAR([1st date]) in VALUES('Table 2'[Date].[Year])&&[1st status] in VALUES('Table 3'[1st status])),[first payment]),CROSSFILTER('Table'[1st date],'Table 2'[Date],None))

Put the following field to the matrix visual

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @hanolby 

Question: how could i create a report in pbi showing how much payment of product C and D is pending in 2024 and 2025?

Suggestion:You can refer to the following sample.

The relationship I have created between the date table and data table

The measure

Measure =
var _1st=SUMMARIZE(ALL('Table'),[Product],'Table'[1st date],'Table'[1st status],'Table'[first payment])
var _2nd=SUMMARIZE(ALL('Table'),[Product],'Table'[2nd date],'Table'[2nd status],'Table'[2nd payment])
var _3rd=SUMMARIZE(ALL('Table'),[Product],'Table'[3rd date],'Table'[3rd  status],'Table'[3rd payment])
var _uniontable=UNION(_1st,_2nd,_3rd)
return CALCULATE(SUMX(FILTER(_uniontable,[Product]=MAX('Table'[Product])&&YEAR([1st date]) in VALUES('Table 2'[Date].[Year])&&[1st status]="pending"&&[Product] in {"c","d"}),[first payment]),CROSSFILTER('Table'[1st date],'Table 2'[Date],None))

Then put the product and measure to a table visual, the result can refer to the following picture.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[hanolby]

This really helped, thanks!!