Forum Discussion

Dhrutivyasa-070's avatar
Dhrutivyasa-070
Helper I
3 years ago
Solved

DAX

Hello Community, 
I have one Taste Rating dataset which contains following column.
I want to compare one primary taster rating with other taster. and i want to slicer for taster name. One is for primary which is in single select and other is secondary taster.

UniqueIDTasterNameTastingScore
100X20
100Y22
100Z20
200X23
200Y23
300X24
300Z26
400Y30
400Z31

 

Required Output 
Primary Taster = x (Suppose)
Then 

TasterNumber of Lot TastedMatch Lot% Match
Y 2150%
z2150%

Example:
Number of Lot Tasted = distinct count of uniqueid which both x and y tasted  
match Lot : out of number of lot tasted which lot rating  match

  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi Dhrutivyasa-070 
    Please refer to attached sample file with the proposed solution.

    Number of Lot Tasted = 
VAR PrimaryTaster = SELECTEDVALUE ( Tasters[Primary Taster] )
VAR Taster = SELECTEDVALUE ( 'Table'[TasterName], PrimaryTaster )
RETURN
    IF (
        Taster <> PrimaryTaster,
        SUMX (
            VALUES ( 'Table'[UniqueID] ),
            INT (
                PrimaryTaster 
                    IN CALCULATETABLE ( 
                        VALUES ( 'Table'[TasterName] ),
                        ALL ( 'Table'[TasterName] )
                    )
            )
        )
    )
    Match Lot = 
VAR PrimaryTaster = SELECTEDVALUE ( Tasters[Primary Taster] )
VAR Taster = SELECTEDVALUE ( 'Table'[TasterName], PrimaryTaster )
RETURN
    IF (
        Taster <> PrimaryTaster,
        SUMX (
            SUMMARIZE ( 'Table', 'Table'[UniqueID], 'Table'[TastingScore] ),
            INT (
                PrimaryTaster 
                    IN CALCULATETABLE ( 
                        VALUES ( 'Table'[TasterName] ),
                        ALL ( 'Table'[TasterName] )
                    )
            )
        )
    )
    % Match = DIVIDE ( [Match Lot], [Number of Lot Tasted] )

4 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi Dhrutivyasa-070 
    Please refer to attached sample file with the proposed solution.

    Number of Lot Tasted = 
VAR PrimaryTaster = SELECTEDVALUE ( Tasters[Primary Taster] )
VAR Taster = SELECTEDVALUE ( 'Table'[TasterName], PrimaryTaster )
RETURN
    IF (
        Taster <> PrimaryTaster,
        SUMX (
            VALUES ( 'Table'[UniqueID] ),
            INT (
                PrimaryTaster 
                    IN CALCULATETABLE ( 
                        VALUES ( 'Table'[TasterName] ),
                        ALL ( 'Table'[TasterName] )
                    )
            )
        )
    )
    Match Lot = 
VAR PrimaryTaster = SELECTEDVALUE ( Tasters[Primary Taster] )
VAR Taster = SELECTEDVALUE ( 'Table'[TasterName], PrimaryTaster )
RETURN
    IF (
        Taster <> PrimaryTaster,
        SUMX (
            SUMMARIZE ( 'Table', 'Table'[UniqueID], 'Table'[TastingScore] ),
            INT (
                PrimaryTaster 
                    IN CALCULATETABLE ( 
                        VALUES ( 'Table'[TasterName] ),
                        ALL ( 'Table'[TasterName] )
                    )
            )
        )
    )
    % Match = DIVIDE ( [Match Lot], [Number of Lot Tasted] )
    • Dhrutivyasa-070's avatar
      Dhrutivyasa-070
      Helper I
      3 years ago

      Hello tamerj1,
      i have facing one more problem.

      in my data set another column add which is rating category. Rating score are categories for increasing match percetage.

      UniqueIDTasterScoreCategory
      100x20a
      100y22b
      100z20a
      200x23b
      200y23b
      300x24a
      300z26b
      400x30a
      400z31a

       

       

      My Primary Taster is X and Secondary Taster is Z nd Catogory A selected in Slicer.

      Required Output :

      Number of Lot Tasted : 3 (Number of Lot both tasted nd primary taster category =a)

      Match : 2.

      All Comparision based on Primary Taster.

      • tamerj1's avatar
        tamerj1
        Community Champion
        3 years ago

        Dhrutivyasa-070 
        I don't fully understand the requiremnt.

        Shall the number of lot tasted ignore the category?

        How did you calculate match = 2?