Forum Discussion

Jtbonner1986's avatar
Jtbonner1986
Helper I
1 year ago

Count Non Matching Rows With One to Many relationships

Hello, 

 

I am trying to count the number of Non Matching rows I have in a pricing table, where the prcing table can contain multiple prices for a single material; 

 

Example;

Program Table 

 

MaterialProgram
123A
456A
789A

 

Pricing Table

 

MaterialPriceDate
123£10001/01/2024
123£6030/01/2024
000£50001/01/2024

 

I want a single measure to calculate how many materials i dont have a pricing for in program 'A'. 

 

in this case it would be 2

 

I essentialy dont care how many enteries a material has in the pricing table, thee could be 100s or dates. I just want to understand how many materials i do not have pricing for 

 

thanks

Need Help

5 Replies

  • Deku's avatar
    Deku
    Super User
    1 year ago

    Countrows( except( values( program[material] ), values( pricing[material] ) )

    • Jtbonner1986's avatar
      Jtbonner1986
      Helper I
      1 year ago

      This only works when there is no relationship between the tables.. 

  • v-veshwara-msft's avatar
    v-veshwara-msft
    Community Support
    1 year ago

    Hi Jtbonner1986 ,
    Thanks for reaching out to Microsoft Fabric Community,
    Just wanted to check in if you have resolved your query, if not please consider the following:

    To count how many materials in Program A do not have any pricing records, you can use the following DAX measure:

    Count of Materials Without Pricing = 
CALCULATE(
    COUNTROWS(
        FILTER(
            VALUES(Program[Material]),
            NOT Program[Material] IN VALUES(Pricing[Material])
        )
    ),
    Program[Program] = "A"
)

    Here is the output:

     

     

    Hope this helps. Please reach out for further assistance.
    If this post helps, then please consider to Accept as the solution to help the other members find it more quickly and a kudos would be appreciated.

     

    Thank you.

     



  • v-veshwara-msft's avatar
    v-veshwara-msft
    Community Support
    1 year ago

    Hi Jtbonner1986 ,

    Just checking in to see if you query is resolved and if any responses were helpful. If so, kindly consider marking the helpful reply as 'Accepted Solution' to help others with similar queries. 

    Otherwise, feel free to reach out for further assistance.

    Thank you.

  • v-veshwara-msft's avatar
    v-veshwara-msft
    Community Support
    1 year ago

    Hi Jtbonner1986 ,

    We’re following up again regarding your query. If it has been resolved, please mark the helpful reply as the Accepted Solution to assist others facing similar challenges.

    If you still need assistance, please let us know.
    Thank you.