Forum Discussion

Farrah24's avatar
Farrah24
Frequent Visitor
3 years ago
Solved

Tables Filter

Hi!

I have table with app name, date, and the zone of the app. I would like to built the table with column represent each zone that contains the name of the application in that zone. How do I filter it using dax?

I'm not familiar with DAX, i would really appreciate if you can provide the solution with the code. Thankyou in advance!

 

 

Need Help
Show and Tell
  • MFelix's avatar
    MFelix
    3 years ago

    Hi Farrah24 ,

     

    Try the following:

    • Create a table with what fi anaylis with the following code:
    AppTotals = GENERATESERIES(1, DISTINCTCOUNT('Index APP'[APP NAME]), 1)
    • Create a table with the zones
    • Related this table with the app table
    • Create the following measure:
    APPS Names = 
VAR _tempTable =
    TOPN (
        MAX ( AppTotals[AppTotals] ),
        SUMMARIZE ( 'Index APP', 'Index APP'[APP NAME], 'Index APP'[Date], Zones[Zone] ),
        'Index APP'[APP NAME], DESC
    )
VAR _RowCount =
    COUNTROWS (
        DISTINCT ( SELECTCOLUMNS ( _tempTable, "DDD", 'Index APP'[APP NAME] ) )
    )
VAR _result =
    IF (
        MAX ( AppTotals[AppTotals] ) <= _RowCount,
        MAXX ( TOPN ( 1, _tempTable ), 'Index APP'[APP NAME] )
    )
RETURN
    IF (
        COUNTROWS ( DISTINCT ( 'Index APP'[Date] ) ) > 1,
        "",
        _result
    )
    • Do your matrix with the following format:
      • Rows: AppTotals from the new table
      • Zone: from the new table - Show items with no data
      • APP Names measure

     

    See file attach.

     

8 Replies

  • MFelix's avatar
    MFelix
    Super User
    3 years ago

    Hi Farrah24 ,

     

    Not sure of the request, can you explain a little better what you need to show?

    • Farrah24's avatar
      Farrah24
      Frequent Visitor
      3 years ago

      Sorry for the unclear explanation, I would like to build a table consist of app name that has been classified by their zone, so there will be column named "zone 1", "zone 2", "zone 3", "zone 4" and the value of the each column is app name included in that zone. For the better understanding please refer to picture below

       

      I've tried build 4 separate table for each zone and try to filter manual for each table, but is there any way to make it more efficient? (build in one table).

       

      I attached pbix i used for this case below. thank you in advance!

      app index file 

       

      • MFelix's avatar
        MFelix
        Super User
        3 years ago

        Hi Farrah24 ,

         

        For this what you can do is use a matrix table:

        • Rows: App Name
        • Columns: Zone
        • Values First APP NAME
        • Turn off Word wrap
        • Turn off totals
        • Hide the first column and you get expected result:

         

        If you need to have some specific ordering you can add some dax to make it in order.

  • Farrah24's avatar
    Farrah24
    Frequent Visitor
    3 years ago

    Hi!

    Sorry for the unclear explanation, I would like to build a table consist of app name that has been classified by their zone, so there will be column named "zone 1", "zone 2", "zone 3", "zone 4" and the value of the each column is app name included in that zone. For the better understanding please refer to picture below

     

    I've tried build 4 separate table for each zone and try to filter manual for each table, but is there any way to make it more efficient? (build in one table).

     

    I attached pbix i used for this case below. thank you in advance!

    app index file