Forum Discussion

Krijgersss's avatar
Krijgersss
Icon for Helper II rankHelper II
2 years ago

distinctcount with a related filter function

Hello i need some help 

i want to make a formule for distinctcount with a related function because i need to filter my distinctcount from a related table (see screenshot)

 

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hello Krijgersss , 

    The easiest way is maybe to modify your relationship to allow filtering in both direction ? 
    becareful, it can alter your model functionnality or performances. 

    If only your disctinctcount need this filter, you can use a DAX Crossfilter function to modify your relationship in a specific measure.






    • Krijgersss's avatar
      Krijgersss
      Icon for Helper II rankHelper II
      2 years ago

      i tried using crossfilter but this gives me extreem loeding times and wrong values is also tried using it in my measure but that also doesnt work. it's because of the model is build i need to distinctcountnoblank on a dimension table and a filter from a fact table

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi Krijgersss 

    you can

    Public Holidays =
SUMX (
    Dim_Emplyee,
    CALCULATE (
        COUNTROWS ( Public_Holidays ),
        USERELATIONSHIP ( Public_Holidays[Country], Dim_Employee[CountryCode] )
    )
)
    • Krijgersss's avatar
      Krijgersss
      Icon for Helper II rankHelper II
      2 years ago

      i tried this but this gives me the same result it doesnt let the filter function work. it's the way the model is build i need to count from a dimension table and i need to filter from a fact tabel i tried using crossfilter but this gives me extreem loeding times and wrong values

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        2 years ago

        Krijgersss 

        Please ignore my above solution. I have edited it by mistake and pasted a solution that was intended for another question in the forum. 

        I remember my original solution included summarizing the fact table by the dimension column. That should work unless there are more information that need to be provided. If you have an active one to many single direction relationship between the two tables, normally this should work. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Anonymous & tamerj1  Thanks for your contribution on this thread.

    Hi Krijgersss ,

    Anonymous  and tamerj1 have provided you some suggestions, but it seems that can't help you get the expected result. Could you please provide some fake data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following link to share the required info:

    How to provide sample data in the Power BI Forum

     

    And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards