Forum Discussion

sjelting's avatar
sjelting
Frequent Visitor
2 years ago
Solved

How to use Text column in Measure without aggregating

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?

"DAX"
  • Anonymous's avatar
    Anonymous
    2 years ago

    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!

6 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Icon for Super User rankSuper User
    2 years ago

    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"
)
  • sjelting's avatar
    sjelting
    Frequent Visitor
    2 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      sjelting
      Frequent Visitor
      2 years ago

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


       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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!