Forum Discussion
KPIs visuals Running incredibly slowly
Desktop and Service
I have a star schema. The model has 9 tables and is 108.61 MB in size.
I have created DAX For DISTINCTCOUNT
and then DAX for SAMEPERIODLASTYEAR to add as the goal value.
However. Some of them arent runing in Desktop and I get "Either a connection cannot be made to the server, or analysis services is not running on the computer specified"
And in Service they came up after about 7 10 minutes. So obviously I can't use what I have at the moment. Has anyone got any tips on what to do here?
You can try to use other functions to mimic the distinct count. Instead of counting from the fact table, we can use it to filter. Try this
Distinct Persons= CALCULATE ( SUMX ( VALUES ( Dimension_Table[PersonID] ), 1 ), Fact_Table )
12 Replies
If memory serves, DISTINCTCOUNT is one of (if not THE) most resource intensive functions. Can you do this count on the Power Query side?
Oh no. Nightmare.
How would you do a distinct count on the Power Query Side when this should happen After the aggregation has happened?
I can't think of any other way to do this
Do you have an example of what you're trying to count?
Fact table
DateKey PersonKey PersonID
01012021 1 P1
01012021 2 P1
01012021 3 P1
01022021 1 P1
01022021 2 P1
01022021 3 P1
Esentially its a DISTINCTCOUNT of the Person ID in the fact table. So here we would get 1
There is specific business logic meaning that there could be multiple entries in a snapshot
Here is what Im thinking. the KPIs are all on specific Snapshot dates.
So In the SQL, If I create a TotalPeoplebySnapshotdate and have a float. If there are two. they will both be 0.5... etc etc. So all add up to 1 for the specific snapshot point. Maybe this will help for this specific issue?
