How to use Text column in Measure without aggregating

[sjelting]

I have a data table with a date and text column. there are multiple rows for the same date. I want to display the rows in a table visual filtered on date. this works well, but I also want to have a "default" filter if no filter on date column is set.

For that i would need to create a measure which uses IF(ISFILTERED(date...), ...) but measures need to return 1 row instead of multiple.

What is the solution in such a case?

[v-huijiey-msft]

Hi @sjelting ,

Thanks for the reply from @Sahir_Maharaj .

I am a little vague about your needs. You mentioned "IF(ISFILTERED(date...), ...)". What do you want to return if the result is TRUE? FalseWhat do you want to return?

You mentioned having multiple rows for the same date, but the measure needs to return 1 row instead of multiple rows. What is the row that needs to be returned?

I would be grateful if you could provide me with your expected results.

I would be grateful if you could provide me with sample data for testing, please remove any sensitive data in advance.

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[sjelting]

i cant upload a sample pbix , so i will add a screenshot:

[v-huijiey-msft · Accepted solution]

Hi @sjelting ,

Please try:

Measure = 
IF (
    NOT ISFILTERED ( 'Table'[Column1] ),
    IF (
        MAX ( 'Table'[Column2] ) IN { "c", "d" },
        MAX ( 'Table'[Column1] ),
        BLANK ()
    ),
    IF ( ISFILTERED ( 'Table'[Column1] ), MAX ( 'Table'[Column1] ) )
)

The final visual effect is shown below:

pbix file is attached.

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[sjelting]

This measure uses the values hard coded., but they change over time, so that is no solution 

[sjelting]

this wont work as it shows only one line if a filter is set even though there might be multiple rows and only the default text if its not set.

[Sahir_Maharaj]

Hello @sjelting,

Can you please try this:

Is Date Filtered = IF(ISFILTERED(TableName[Date]), "Filtered", "Not Filtered")

Display Text = 
VAR isFiltered = [Is Date Filtered]
RETURN
IF(
    isFiltered = "Filtered",
    MAX(TableName[TextColumn]), 
    "Default Text"
)