Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

need to convert an sql code to dax.

Hi Everyone,    I'm trying to convert an SQL code to DAX. The result of the query is to give a list of distinct count of IDs as a result of a join with another table where the contents of a conditi...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi Anonymous 

     

    Do you create a relationship between the fact table and dim table on resident_key columns in the model in Power BI? If you do, you can create the following measure to get the distinct count. I assume that the relationship is one (dim table) to many (fact table).  

    Distinct Count of Id = CALCULATE(DISTINCTCOUNT(dim_resident[resident_id]),RELATEDTABLE(fact_resident_daily))

     

    I have attached a sample file at bottom. 

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

v-jingzhang's avatar
v-jingzhang
Community Support
4 years ago

Hi Anonymous 

 

Do you create a relationship between the fact table and dim table on resident_key columns in the model in Power BI? If you do, you can create the following measure to get the distinct count. I assume that the relationship is one (dim table) to many (fact table).  

Distinct Count of Id = CALCULATE(DISTINCTCOUNT(dim_resident[resident_id]),RELATEDTABLE(fact_resident_daily))

 

I have attached a sample file at bottom. 

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.