Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Distinct/ Distinct count by key category

Hi everyone, I need your help😥   I would like to add red column to my table. Key of the group for distinction would be "show" and "FY".     shop item FY item count itme name ...
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Anonymous , try like

    Measure = CALCULATE(COUNTX(VALUES('Item'[Item]),('Item'[Item])),ALLEXCEPT('Item','Item'[shop] , Item'[FY] ))

    Measure = CALCULATE(CONCATENATEX(VALUES('Table'[Item]),('Table'[Item])),ALLEXCEPT('Table','Item'[shop] , 'Item'[FY] ))

Anonymous's avatar
Anonymous
Not applicable
6 years ago

amitchandak 

thank you for your help.😀

 

I've trid but Ive got an error on following 😅

calculate(concatenatex(Table,distinct(Table[item])), allexcept(Table[shop]))

 

so i change to following.

calculate(concatenatex(Table,distinct(Table[item])), allexcept(Table,Table[shop]))

but when i add to the vidual it shows another error.

"can't display the vidual."

 

 

amitchandak's avatar
amitchandak
Super User
6 years ago

Anonymous , Try like this tested on my table 

Measure = CALCULATE(CONCATENATEX(VALUES('Item'[Category]),('Item'[Category])),ALLEXCEPT('Item','Item'[Brand]))

Measure = CALCULATE(CONCATENATEX(VALUES('Table'[Item]),('Table'[Item])),ALLEXCEPT('Table','Item'[shop]))
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    amitchandak 

     

    Thank you !😆

     

    Would you please advice me how to add a key to distinct?

    I would like to distinct shop and FY.

     

     

    • amitchandak's avatar
      amitchandak
      Super User
      6 years ago

      Anonymous , Try like

      Measure = CALCULATE(COUNTX(VALUES('Item'[Category]),('Item'[Category])),ALLEXCEPT('Item','Item'[Brand]))
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    amitchandak 

     

    Thank you for your help😀

     

    I would like to add a condition shop and FY in one measure.

    I've triedlike this but did not work...

    Measure = CALCULATE(COUNTX(VALUES('Item'[Item]),('Item'[Item])),ALLEXCEPT('Item','Item'[shop] &&'Item'[FY] ))

    Measure = CALCULATE(CONCATENATEX(VALUES('Table'[Item]),('Table'[Item])),ALLEXCEPT('Table','Item'[shop] && 'Item'[FY] ))

    • amitchandak's avatar
      amitchandak
      Super User
      6 years ago

      Anonymous , try like

      Measure = CALCULATE(COUNTX(VALUES('Item'[Item]),('Item'[Item])),ALLEXCEPT('Item','Item'[shop] , Item'[FY] ))

      Measure = CALCULATE(CONCATENATEX(VALUES('Table'[Item]),('Table'[Item])),ALLEXCEPT('Table','Item'[shop] , 'Item'[FY] ))

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        amitchandak 

        that was perfect😂

        Thank you so much😌