DAX Filter on matrix not working when using ALLSELECTED in measure

Nico_Saar — Thu, 02 Feb 2023 09:49:48 GMT

Hi everyone,

I need some help solving the following problem:

I have 3 tables (1 fact + 2 dimensions) which contain the following columns:

Fact_Table
ProductID	Grade	DefectCode	DateID
1	B	D1	20230101
1	B	D4	20230101
1	C	D4	20230101
2	B	D1	20230102
3	B	D2	20230105
3	C	D3	20230105
3	C	D4	20230105
4	B	D2	20230106
5	B	D1	20230114
5	C	D1	20230114

Dim_Table
DefectCode	DefectDescription
D1	Defect 1
D2	Defect 2
D3	Defect 3
D4	Defect 4

The second dimension table is a standard date dimension.

My goal is to build a matrix visual which looks like this:

Matrix
ProductID	Defect 1	Defect 2	Defect 3	Defect 4
1	B	A	A	C
2	B	A	A	A
3	A	B	C	C
4	A	B	A	A
5	C	A	A	A

The column "ProductID" from my fact table is used as "Rows" in my matrix visual
The column "DefectDescription" from my dimension table is used as a column in my matrix visual
The column "Grade" should be used as values.
- IMPORTANT: there could be multiple grades for one ProductID and for the same DefectCode. E.g. for ProductID = 1 and DefectCode = D4 there are 2 grades, "B" & "C".
  In this case, the "lowest" grade should be selected -> C
- If there is no Grade available for a DefectCode, I want to show "A" instead of a blank cell.

In order to achieve this a created the following measure which I use in the matrix visual:

Grade =

VAR result =

CALCULATE (

MAX ( 'Fact_Table'[Grade] ),

FILTER ( ALLSELECTED ( 'Fact_Table' ),

'Fact_Table'[Grade] IN VALUES ( 'Fact_Table'[Grade] )

)

RETURN

IF ( result <> BLANK (), result, "A+" )

On the first sight this works pretty well but a problem occures as soon as I want to filter the data inside the matrix using a slicer. (e.g. Date) Date date slicers is using the date dimension with a relationship to my fact table. When I set the date range starting at 20230102, all values for ProductID = 1 will bet set a "A". I know that the visual is supposed the replace not existing values with "A" but in this case, I don't want to show ProductID = 1 at all.

I think that the problem is located in the "ALLSELECTED" part of the FILTER function but I can't figure out what to change in order to get the result needed.

Best regards

Re: DAX Filter on matrix not working when using ALLSELECTED in measure

Anonymous — Wed, 22 Feb 2023 08:43:45 GMT

Hi Nico_Saar ,

Here I create a sample to have a test.

I think you can try this code to create a measure.

M_Grade = VAR _Count = CALCULATE ( COUNT ( Fact_Table[DefectCode] ), ALLEXCEPT ( Fact_Table, Fact_Table[ProductID], Dim_Date[DateID] ) ) VAR _LowestGrade = CALCULATE ( MAX ( Fact_Table[Grade] ) ) RETURN IF ( _Count = 0, BLANK (), IF ( ISBLANK ( _LowestGrade ), "A", _LowestGrade ) )

Result is as below.

By Default:

Select "20230102" in slicer:

Best Regards,
Rico Zhou

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

topic DAX Filter on matrix not working when using ALLSELECTED in measure in DAX Commands and Tips

DAX Filter on matrix not working when using ALLSELECTED in measure

Re: DAX Filter on matrix not working when using ALLSELECTED in measure