Forum Discussion

Beho's avatar
Beho
Icon for Helper I rankHelper I
2 years ago

Top 2 Failed Categories

Hello,   I have a scoring table with multiple categories and I am trying to have visibility on top 2 failed categories per day, month, etc.   Name Date Category 1 Category 2  Category 3 C...
Need Help
v-zhangti's avatar
v-zhangti
Icon for Community Support rankCommunity Support
2 years ago

Hi, Beho 

 

You can try the following methods. Check the first two columns-Transform-Unpivot Other column:

Date table:

Measure:

Count = CALCULATE(COUNT('Table'[Name]),FILTER(ALLEXCEPT('Table','Table'[Category]),[Value]="Failed"&&MONTH([Date])=SELECTEDVALUE('Date'[Month])))

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

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

Beho's avatar
Beho
Icon for Helper I rankHelper I
2 years ago

Hi v-zhangti, My issue is the report has a lot of visuals already set up, once I unpivot, the duplicates mess up the numbers.

Can I create another table and somehow have the dates, names, concatenated column, and then split in that table instead. This way it doesn't touch the original table? 

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    2 years ago

    Hi, Beho 

     

    Of course. You can copy and paste a copy of your original table in power query. Do the Unpivot operation in the copied data table.

     

    Best Regards

    • Beho's avatar
      Beho
      Icon for Helper I rankHelper I
      2 years ago

      Quick question, if I copy the table, does the new table stay updated? I mean if the original table gets new rows or modified rows, does the copy update automatically? Thanks