Forum Discussion

ettekul29's avatar
ettekul29
Regular Visitor
1 year ago
Solved

DAX Consistency Check

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!

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

3 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    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?

  • ettekul29's avatar
    ettekul29
    Regular Visitor
    1 year ago

    Hello danextian,

     

    Please see below:

     

    ViKeyMatnrGTEFGHIJKLMGWOGW Check 
    70178410005053990178407     70178410005053990178407     HE1.00019.0007.8607.86023.400CM 1,445.643CCM211.580G    
    70178410005053990178407     70178410015053990178407     HE1.00019.0007.8607.86023.400CM 1,445.643CCM211.580G    
    70178410005053990178407     70178410025053990178407     HE1.00019.0007.8607.86023.400CM 1,445.643CCM211.580G    
    70178410005053990178407     70178410035053990178407     HE1.00019.0007.8607.86023.400CM 1,445.643CCM211.580G    
    70178410005053990178407     70178410045053990178407     HE1.00019.0007.8607.86023.400CM 1,445.643CCM211.580G    
    70178410005053990178407     70178410055053990178407     HE1.00019.0007.8607.86023.400CM 1,445.643CCM211.580G    
    70178410005053990178407     70178410065053990178407     HE1.00019.0007.8607.86023.400CM 1,445.643CCM211.580G    
    70178580005053990178407     70178580005053990178407     HE1.0006.0007.8607.86023.400CM 1,445.643CCM211.580G    
    70178580005053990178407     70178580015053990178407     HE1.0006.0007.8607.86023.400CM 1,445.643CCM211.580G    
    70178580005053990178407     70178580025053990178407     HE1.0006.0007.8607.86023.400CM 1,445.643CCM211.580G    
    70178580005053990178407     70178580035053990178407     HE1.0006.0007.8607.86023.400CM 1,445.643CCM211.580G    
    70178580005053990178407     70178580045053990178407     HE1.0006.0007.8607.86023.400CM 1,445.643CCM206.730G  Errorerror as for the same GT=5053990178407, there are multiple GWs
    70183720005053990178407     70183720005053990178407     HE1.0006.0007.8607.86023.400CM 1,445.643CCM206.730G  Errorerror as for the same GT=5053990178407, there are multiple GWs
    70183720005053990178407     70183720015053990178407     HE1.0006.0007.8607.86023.400CM 1,445.643CCM206.730G  Errorerror as for the same GT=5053990178407, there are multiple GWs
    70002480005053990107278     70002480005053990107278     HE1.00012.0007.8607.8608.700CM 537.483CCM63.650G   no errors as GW is aligned at GT level
    70002480005053990107278     70002480205053990107278     HE1.00012.0007.8607.8608.700CM 537.483CCM63.650G   
    70002480005053990107278     70002480265053990107278     HE1.00012.0007.8607.8608.700CM 537.483CCM63.650G   
    70002480005053990107278     70002480295053990107278     HE1.00012.0007.8607.8608.700CM 537.483CCM63.650G   
    70002480005053990107278     70002480305053990107278     HE1.00012.0007.8607.8608.700CM 537.483CCM63.650G   
    70002480005053990107278     70002480315053990107278     HE1.00012.0007.8607.8608.700CM 537.483CCM63.650G   
    70002480005053990107278     70002480325053990107278     HE1.00012.0007.8607.8608.700CM 537.483CCM63.650G   
    70162860005053990107278     70162860005053990107278     HE1.00012.0007.8607.8608.700CM 537.483CCM63.650G   
    70162860005053990107278     70162860015053990107278     HE1.00012.0007.8607.8608.700CM 537.483CCM63.650G   
    70162860005053990107278     70162860025053990107278     HE1.00012.0007.8607.8608.700CM 537.483CCM63.650G   

     

    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!

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.