Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I'm looking for a bit of help. I'm new to using powerbi and come from a background on excel so as you can imagine, I'm having a little trouble adjusting.
I want to be able to select a date range and have a visual show me how often customers had to call me.
something like:-
1 call = "this many"
2 calls = "this many"
3 calls = "this many"
4 calls = "this many"
What I have so far is a table with all my call data with an identifier and date/time for each customer contact and a seperate table distinctly listing my identifiers. I want to create a measure on the second table that will tell me how many times each of the distinct identifiers appears in the first table. Idea being that when I select a date range on my visual, the measure on table 2 will adjust to count within that. I feel as though I'm missing something very important though.
I've tried to do the following:-
Measure = COUNTROWS( FILTER ('table1' , 'table1' [identifier] = 'table2' [identifier]))
This gives me the following error:
"A single value for column 'Account Number (Account)' in table 'Unique Accounts Table' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
I get a red underline under 'table2'[identifier] as well.
Anyone able to point me in the right direction with this one?
Thanks
Solved! Go to Solution.
Hi @Anonymous,
Could you try the formula below to see if it works.
Measure = IF ( HASONEVALUE ( 'table2'[identifier] ), COUNTROWS ( FILTER ( 'table1', 'table1'[identifier] = VALUES ( 'table2'[identifier] ) ) ), COUNTROWS ( 'table1' ) )
Regards
Hi @Anonymous,
Could you try the formula below to see if it works.
Measure = IF ( HASONEVALUE ( 'table2'[identifier] ), COUNTROWS ( FILTER ( 'table1', 'table1'[identifier] = VALUES ( 'table2'[identifier] ) ) ), COUNTROWS ( 'table1' ) )
Regards
Hi v-ljerr-msft,
Thanks for for your suggestion. This has worked but not the way I hoped. When I come to filter by date on a visual using this measure, it doesn't re-adjust itself to look at only the data in table 1 between the 2 dates I select. I'm maybe misunderstanding how measures work completely so I'll go back to the books and learn some more.
I can get the view I'm looking for now however if I were to now find away to create a new table from table 1 between date ranges which I'm sure I can work out. I'll accept this one as answered and thank you once again for your help.
Kind Regards
Scottit
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |