Slicer with a measure

[martaredondo]

Hi guys,

I have created a measure "Paid/Unpaid" and I want to filter the page based on that measure. Do you have any idea how can I do this? Here is the table:

Thank you!

[IanCockcroft]

Cant you use  column?

[sturlaws]

Hi,

First you need to create a stand-alone table with 1 column and the values Paid and Unpaid on the rows.

You can then create this measure

statusSelect =
IF (
    HASONEVALUE ( 'Status'[Status] );
    COUNTROWS ( FILTER ( 'Table'; SELECTEDVALUE ( 'Status'[Status] ) = [Paid/Unpaid] ) );
    1
)

and add it to the filter section of your page. Set it to filter on statusSelect greater or equal than 1

[martaredondo]

Hey  @sturlaws ,

Thanks for your answer. I did what you suggested and it worked! The problem is, I have other visualizations like bar charts, tachometer and cards and the only one I can filter with the Unpaid/Paid measure is the bar chart.. I tried to insert the measure in the cards but it won't allow me to choose how to filter. Do you know how can I solve this?

Thanks a lot,

Marta

[sturlaws]

Does Paid/Unpaid have to be a measure? Or could it be converted to a calculated column? Unless Paid/Unpaid depends on user inputs, it could be converted to a calculated column. This will make what you want to achieve much easier, compared to setting up filters based on the measure

[martaredondo]

The problem of using a column instead of a measure is that I get repeted rows (I want to have one row by subproject):

With the measure I get the aggregate for each subproject:

This is why I would like to use measure instead of column..

[sturlaws · Accepted solution]

if you write your code like this

Paid/Unpaid =
IF (
    CALCULATE (
        SUM ( table[paid] );
        FILTER ( ALL ( table ); table[subproject] = _subproject )
    )
        - CALCULATE (
            SUM ( table[unpaid] );
            FILTER ( ALL ( table ); table[subproject] = _subproject )
        ) >= 0;
    "Paid";
    "Unpaid"
)

you will sum the values for each subproject, so each subproject will have only Paid or Unpaid.

Since you have not posted the code, nor a sample file, the code above contains some guesswork. It might be more complicated than that, depending on your model and requirements. But the principle is there, you can calculate across rows for a subproject.