Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have the following table in PowerBI:
The entries come from a Folder source, where I have multiple CSVs with the same structure that gets combined into a single query inside PBI. I may have the same hostname in more than one CSV, so I use the "Count of Hostname" and sort it to see the duplicated hostnames, but I also want to count the number of hostnames where the "count of hostnames" is more than 1, so I can display that in a Card view. How can I do that?
Solved! Go to Solution.
Hey,
based on your sample data I created the following calculated column
MoreThanOnce = IF( CALCULATE( COUNTROWS('Table1') ,ALLEXCEPT('Table1','Table1'[from]) ) >1 ,"TRUE" ,"FALSE" )
This results to
Now you can use this colum to slice your data, and also in charts. Putting the column on the xaxis, [from] to the value (change the aggregation to "Count (Distinct)", or create measure like this
distinct froms with multiple appearance = CALCULATE( DISTINCTCOUNT('yourtable'[from]) ,'yourtable'[MoreThanOnce] = "TRUE" )
Hope this is what you are looking for
Regards
Tom
Hi @JChris,
Try this calculated field formula
=CALCULATE(COUNTROWS(Data),FILTER(VALUES(Data[Host]),COUNTROWS(Data)>1))
Hope this helps.
Hey,
can you please make sample data, I have to admit that I do not understand what you are askinf for.
Regards
Tom
2017-10-09.csv
to,cc,hostname
alice@company.com,bob@company.com,US001
diana@company.com,willy@company.com,US017
mike@company.com,bob@company.com,US032
...
2017-10-10.csv
to,cc,hostname
alice@company.com,bob@company.com,US001
diana@company.com,willy@company.com,US017
wallace@company.com,stuart@company.com,US037
...
combined
to,cc,hostname
alice@company.com,bob@company.com,US001
diana@company.com,willy@company.com,US017
mike@company.com,bob@company.com,US032
alice@company.com,bob@company.com,US001
diana@company.com,willy@company.com,US017
wallace@company.com,stuart@company.com,US037
...
Both CSVs will be inside the folder "input" and I use that folder as source in PBI, using combine + edit, so I have all data in one query with only one header. I then proceed and create the table visual, and add one "count of hostnames" column. In this example, alice@company.com and diana@company.com will have a count of 2 hostnames, as they appear twice in the combined source query.
I want to count how many people have more than 1 as "count of hostname". I already can see who they are, I just want to know how many they are without having to count myself.
Hi @JChris,
The solution @TomMartens posted is right, you should create a calculated column "count of hostnames" in sample table rather than in table visualization.
count of hostnames = CALCULATE ( COUNTROWS ( 'Table1' ), ALLEXCEPT ( 'Table1', 'Table1'[from] ) )
Then create a measure using the formula and display it in card visual.
multiple appearance = CALCULATE ( DISTINCTCOUNT ( 'yourtable'[from] ), FILTER ( 'yourtable', 'yourtable'[MoreThanOnce] > 1 ) )
Best Regards,
Angelia
Hey,
based on your sample data I created the following calculated column
MoreThanOnce = IF( CALCULATE( COUNTROWS('Table1') ,ALLEXCEPT('Table1','Table1'[from]) ) >1 ,"TRUE" ,"FALSE" )
This results to
Now you can use this colum to slice your data, and also in charts. Putting the column on the xaxis, [from] to the value (change the aggregation to "Count (Distinct)", or create measure like this
distinct froms with multiple appearance = CALCULATE( DISTINCTCOUNT('yourtable'[from]) ,'yourtable'[MoreThanOnce] = "TRUE" )
Hope this is what you are looking for
Regards
Tom
User | Count |
---|---|
86 | |
84 | |
69 | |
67 | |
55 |
User | Count |
---|---|
125 | |
100 | |
90 | |
84 | |
66 |