Forum Discussion

farinabrn's avatar
farinabrn
Regular Visitor
8 years ago
Solved

Distinct between 2 tables

Hi everyone,   I am start to learning PBI and on my work i have a problem, Can you help me?   I'm trying to build a dashboard with a stacked bar chart where will be shown a percentage of stores w...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi farinabrn,

    I have wrote this sql query trying to find a way to convert it to DAX but i'm noob lol

     

    SELECT v.YEAR, v.WEEK, v.REGION, v.COMPANY, v.STORE
    ((SELECT COUNT(1) FROM PRODUCT) - COUNT(DISTINCT p.ID)) AS prod
    FROM SALES v
    LEFT JOIN PRODUCT p ON p.ID = v.PRODUCT_ID
    GROUP BY v.YEAR, v.WEEK, v.REGION, v.COMPANY, v.STORE

    Instead of converting the sql query to DAX, you should be able to directly use the sql query in SQL statement option when GetData to a new table to your model. :smileyhappy:

     

     

    Regards

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
8 years ago

Hi farinabrn,

I have wrote this sql query trying to find a way to convert it to DAX but i'm noob lol

 

SELECT v.YEAR, v.WEEK, v.REGION, v.COMPANY, v.STORE
((SELECT COUNT(1) FROM PRODUCT) - COUNT(DISTINCT p.ID)) AS prod
FROM SALES v
LEFT JOIN PRODUCT p ON p.ID = v.PRODUCT_ID
GROUP BY v.YEAR, v.WEEK, v.REGION, v.COMPANY, v.STORE

Instead of converting the sql query to DAX, you should be able to directly use the sql query in SQL statement option when GetData to a new table to your model. :smileyhappy:

 

 

Regards