DAX Consistency Check

[ettekul29]

Hi everyone,

I'm trying to migrate a consistency check from excel to dax, and while lookupvalue doesn't work anymore as I have multiple values in the fields I'm working with, I tried using the below:

CALCULATE(
    DISTINCTCOUNT(Table1[Weight]),
    ALLEXCEPT(Table1, Table1[Key])
)

This is to check whether I have multiple weight values at a certain level (e.g. [Key]).

As the formula returns 11 distinct weights where I only have 2, do you have any suggestions on how could I make this work?

Thanks a lot!

[Anonymous · Accepted solution]

Thanks for the reply from danextian , please allow me to provide another insight:

Hi,  @ettekul29 

Thanks for reaching out to the Microsoft fabric community forum.

I apologise, but I couldn't find the Weight column in the sample data you provided, so I'm not entirely sure what you mean by "As the formula returns 11 distinct weights where I only have 2."

In the following example, I will use the Matnr column instead of the Weight column:

1.If you only need to count the number of distinct Key values, I suggest using the following measure:

Measure = DISTINCTCOUNT ( 'Table1'[Key])

2.If your requirement is to understand why the values in the visualisation are all the same, you might need to consider replacing the ALLEXCEPT() function part with FILTER('Table1', 'Table1'[Key] = MAX('Table1'[Key])):

Measure2 = 
CALCULATE (
    DISTINCTCOUNT ( 'Table1'[Matnr] ),
    FILTER ( 'Table1', 'Table1'[Key] = MAX ( 'Table1'[Key] ) )
)

3.Here's my final result, which I hope meets your requirements.

Please note that Measure3 is your original DAX, and all the above DAX calculations are implemented as measures.

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

We recommend uploading it to GitHub and sharing the link with us.When uploading a file, please be careful to delete sensitive information.

For questions about uploading data, you can try the following links:

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Solved: How to upload PBI in Community - Microsoft Fabric Community

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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

[ettekul29]

Hello @danextian,

Please see below:

Vi	Key	Matnr	GT	E	F	G	H	I	J	K	L	M	GW	O	GW Check
7017841000	5053990178407	7017841000	5053990178407	HE	1.000	19.000	7.860	7.860	23.400	CM	1,445.643	CCM	211.580	G
7017841000	5053990178407	7017841001	5053990178407	HE	1.000	19.000	7.860	7.860	23.400	CM	1,445.643	CCM	211.580	G
7017841000	5053990178407	7017841002	5053990178407	HE	1.000	19.000	7.860	7.860	23.400	CM	1,445.643	CCM	211.580	G
7017841000	5053990178407	7017841003	5053990178407	HE	1.000	19.000	7.860	7.860	23.400	CM	1,445.643	CCM	211.580	G
7017841000	5053990178407	7017841004	5053990178407	HE	1.000	19.000	7.860	7.860	23.400	CM	1,445.643	CCM	211.580	G
7017841000	5053990178407	7017841005	5053990178407	HE	1.000	19.000	7.860	7.860	23.400	CM	1,445.643	CCM	211.580	G
7017841000	5053990178407	7017841006	5053990178407	HE	1.000	19.000	7.860	7.860	23.400	CM	1,445.643	CCM	211.580	G
7017858000	5053990178407	7017858000	5053990178407	HE	1.000	6.000	7.860	7.860	23.400	CM	1,445.643	CCM	211.580	G
7017858000	5053990178407	7017858001	5053990178407	HE	1.000	6.000	7.860	7.860	23.400	CM	1,445.643	CCM	211.580	G
7017858000	5053990178407	7017858002	5053990178407	HE	1.000	6.000	7.860	7.860	23.400	CM	1,445.643	CCM	211.580	G
7017858000	5053990178407	7017858003	5053990178407	HE	1.000	6.000	7.860	7.860	23.400	CM	1,445.643	CCM	211.580	G
7017858000	5053990178407	7017858004	5053990178407	HE	1.000	6.000	7.860	7.860	23.400	CM	1,445.643	CCM	206.730	G	Error	error as for the same GT=5053990178407, there are multiple GWs
7018372000	5053990178407	7018372000	5053990178407	HE	1.000	6.000	7.860	7.860	23.400	CM	1,445.643	CCM	206.730	G	Error	error as for the same GT=5053990178407, there are multiple GWs
7018372000	5053990178407	7018372001	5053990178407	HE	1.000	6.000	7.860	7.860	23.400	CM	1,445.643	CCM	206.730	G	Error	error as for the same GT=5053990178407, there are multiple GWs
7000248000	5053990107278	7000248000	5053990107278	HE	1.000	12.000	7.860	7.860	8.700	CM	537.483	CCM	63.650	G		no errors as GW is aligned at GT level
7000248000	5053990107278	7000248020	5053990107278	HE	1.000	12.000	7.860	7.860	8.700	CM	537.483	CCM	63.650	G
7000248000	5053990107278	7000248026	5053990107278	HE	1.000	12.000	7.860	7.860	8.700	CM	537.483	CCM	63.650	G
7000248000	5053990107278	7000248029	5053990107278	HE	1.000	12.000	7.860	7.860	8.700	CM	537.483	CCM	63.650	G
7000248000	5053990107278	7000248030	5053990107278	HE	1.000	12.000	7.860	7.860	8.700	CM	537.483	CCM	63.650	G
7000248000	5053990107278	7000248031	5053990107278	HE	1.000	12.000	7.860	7.860	8.700	CM	537.483	CCM	63.650	G
7000248000	5053990107278	7000248032	5053990107278	HE	1.000	12.000	7.860	7.860	8.700	CM	537.483	CCM	63.650	G
7016286000	5053990107278	7016286000	5053990107278	HE	1.000	12.000	7.860	7.860	8.700	CM	537.483	CCM	63.650	G
7016286000	5053990107278	7016286001	5053990107278	HE	1.000	12.000	7.860	7.860	8.700	CM	537.483	CCM	63.650	G
7016286000	5053990107278	7016286002	5053990107278	HE	1.000	12.000	7.860	7.860	8.700	CM	537.483	CCM	63.650	G

Vi=left(C,7)&"000"

B=D

GW Check=IF(TRIM(D)="","",IF(AND(N<>"",VLOOKUP(D,B:N,13,0)=N),"","Error"))

Thank you!

[danextian]

Hi @ettekul29 

What do you mean by this?

As the formula returns 11 distinct weights where I only have 2, do you have any suggestions on how could I make this work?

Please post a workable sample data (not an image), your expected result from that and the reasoning behind?