topic Re: DAX in DAX Commands and Tips

DAX

Dhrutivyasa-070 — Tue, 20 Jun 2023 11:40:19 GMT

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.

UniqueID	TasterName	TastingScore
100	X	20
100	Y	22
100	Z	20
200	X	23
200	Y	23
300	X	24
300	Z	26
400	Y	30
400	Z	31

Required Output
Primary Taster = x (Suppose)
Then

Taster	Number of Lot Tasted	Match Lot	% Match
Y	2	1	50%
z	2	1	50%

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

Re: DAX

tamerj1 — Tue, 20 Jun 2023 13:55:27 GMT

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] )

Re: DAX

Dhrutivyasa-070 — Wed, 21 Jun 2023 06:13:24 GMT

Thank You Very Much @tamrej1
Its working Properly in my Dataset.

Re: DAX

Dhrutivyasa-070 — Fri, 23 Jun 2023 08:52:07 GMT

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.

UniqueID	Taster	Score	Category
100	x	20	a
100	y	22	b
100	z	20	a
200	x	23	b
200	y	23	b
300	x	24	a
300	z	26	b
400	x	30	a
400	z	31	a

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.

Re: DAX

tamerj1 — Sun, 25 Jun 2023 11:12:30 GMT

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

Shall the number of lot tasted ignore the category?

How did you calculate match = 2?