CALCULATE DISTINCT value in a measure in DAX

[Anonymous]

Hello,

I have a measure that returns the number of distinct visitors from a column for each zone (or all zones depending on the applied filter). The thing is, I have to apply a ratio to that value (before it is returned), depending on the zone. But as long as a measure cannot determine a unique value from a column, I have no idea how to use that ratio.

For example, I have 3 zones in wich I want to measure the number of distinct visitors for each while appliying the ratio depending on the zone ( 100 people * 1.2 for the first zone, 250 people * 1 in the second and 325 * 0.6 in the last) and then I want to make a sum of those numbers or something.

nb_devices = 

var nb = CALCULATE(DISTINCTCOUNT(meraki_observation_zone[client_mac]))

// ratio = CALCULATE(DISTINCT(zone_ratio[ratio]);FILTER(zone_ratio; zone_ratio[zone]=meraki_observation_zone[zone]))
// nb2 = nb * ratio

return IF( ISNUMBER(nb) ; nb ; 0 )

Thanks in advance! 

[tex628]

Try using SelectedValue() for this. 

[Anonymous]

So I tried replacing the whole line for the variable ratio with :

var ratio = SELECTEDVALUE(meraki_observation_zone[ratio])

Actually, it seems to work for distinct zones, but i cannot make a sum of the distinct visitors (zone 1 + zone 2, etc...)

Would you know how to do it?

Thank you a lot!

[tex628]

That's actually more problematic than it seems 😄 

I assume you want to make 1 measure that includes all zones and not hardcode each zone?

[Anonymous]

Exactly! 

Indeed, I need to make a measure that can be filtered or subdivided as in an array. I need to apply a ratio to modulate the number of visitors for each zone and then return the number of visitors (for each zone separately or all zones together). I'm working on it for almost a week 

[tex628]

I'm gonna need lunch before this, but i think i have a solution in mind! 🙂

[Anonymous]

Right, thank you so much 

[v-cherch-msft]

Hi @Anonymous

You may set the relationship for the two tables. Then you may create the measure like below.Attached the sample file.If it is not your case,please share your sample data and expected output.

Measure = CALCULATE(DISTINCTCOUNT(Zone[visitors]))

Measure 2 = [Measure]*SELECTEDVALUE(Ratio[ratio])

How to Get Your Question Answered Quickly

Regards,

Cherie

[Anonymous]

Hi,

Actually, both you solutions do not allow me to get the expected results. I am sorry if this looks a bit fetched, but here is the mission. I can't tell too much, but we count people in an area made of 1 or more zones.

If we want to know how many people were here, we do that DISTINCTCOUNT(), so we don't count them more than once.
Now, let's say at some time, I have 3 people in zone 1 and 4 in another one, that makes a total of 5 people in the whole area. And then we do the same for another period. It is possible that some people were twice in the area at two different moments and we want to count them only once if we do not filter the period (so we get a total for all the time). And using a DISTINCTCOUNT() in a measure make the counting way more flexible than doing a SUM() over fixed values (number of visitors for each hour) in a column.

This is why I can't rely on a column to count people and I also may use the measure in a graph or to make other mathematical operations in other measures.

The actual problem is that the way we count people oblige us to say "well there were 2 times less people than the data is telling us". So we say : in this zone it's 0.5 times the number of visitors, in this one, we have 0.7 times the number of visitors, etc... And we should get a total based on the number of visitors * the ratio for each zone (visitors in zone 1 * 0.5 + visitors in zone 2 * 0.7 = total visitors on the whole area). 

I am long explaining the problem but I think you might get the idea 

Here is the expected result format :

And there is a sample of the table in wich we count people

Again, I apologize if I take a long time explaining the problem, but that is also the reason why I can't figure out a solution.

So thank you again!  

[tex628]

When you filter this on period in the applikation, which is the smallest period that you will use? 

Month, week, day? 

[Anonymous]

It's hourly counting and we assume that there are this many people on an hour and then this many people the next hour, etc...

Sorry for the waiting, I've been working on other stuff because we couldn't find a solution, but I still have to solve this problem.

Thanks again for the time you spent helping me! 

[tex628]

OK! 

Create a calculated column in your table:

Column = 
VAR zone = meraki_observation_zone[Zone]
return
CALCULATE(DISTINCTCOUNT(meraki_observation_zone[client_mac]);ALL(meraki_observation_zone);meraki_observation_zone[Zone]=zone)

Then create another calculated column where you multiply the ratio with the previous column, should look like this:



Then finalize with this measure:

Summary = 
SUMX(CALCULATETABLE(VALUES(meraki_observation_zone[Column2]));meraki_observation_zone[Column2])

[Anonymous]

I tried a solution like this one, but I cannot use a column since I need to calculate a distinct number of visitors on different periods 

Actually, I have no idea if this is either possible or not, but I must use a measure to count distinct vistors (ex total distinct visitors on a day long period or 3 days and the measure won't return the same numbers than if I used a column because some visitors may be counted 2 or more times (doing a sum in a column))

[tex628]

Measure = SUMX(SUMMARIZECOLUMNS(meraki_observation_zone[Zone];"Ratio";SELECTEDVALUE(meraki_observation_zone[Ratio]);"Count";DISTINCTCOUNT(meraki_observation_zone[client_mac]));[Count]*[Ratio])

Try this! 🙂

[tex628]

Yup that wont work with a period filter, my bad! 

Give me a moment to think! 🙂

[Anonymous]

Would you mean to replace DISTINCT() with SELECTEDVALUE() or so?

I'm sorry, I am new to Power BI

[tex628]

Since you want to multiply the measure I'm assuming there is supposed to be a single value in ratio at the moment of the multiplication which would mean that selected value would be a good solution. 

Just changing it should work. 

Measure =
CALCULATE(SELECTEDVALUE(zone_ratio[ratio]);
zone_ratio[zone]=meraki_observation_zone[zone])
)

If this returns blank it should mean that there is more than one distinct ratio and the function will not be able to determine which one you are after.

[Anonymous]

This is what it does actually (and yes, I have only 1 ratio for each zone, so no problem here) :

And using the SELECTEDVALUE() function, I cannot get a total of the visitors.