Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi everyone,
I would like to calculate how many names that are not in every region.
Best regards
Tito
Data
Result:
Solved! Go to Solution.
Hi @Tito
Try this DAX ,
Hi @Tito
Find the updated measure
Count Blanks =
var _totalRegion = CALCULATE(DISTINCTCOUNT('Table'[Name]),ALL('Table'))
return
IF(
HASONEVALUE('Table'[Region]),
(_totalRegion -
CALCULATE(COUNTROWS('Table'),VALUES('Table'[Region]) )),
BLANK())
Hi @Tito
Try this DAX ,
Hi @Rohit11 ,
thank you very much! that worked.
If a name (A,B,C..) appears several times in the Name column, what should I change in Measure. Thank you!
Best regards
Tito
Try this
Measure = VAR NorthNotPresentCount = COUNTROWS(FILTER(ALL('Table'[Region]), 'Table'[Region] <> "North")) VAR WestNotPresentCount = COUNTROWS(FILTER(ALL('Table'[Region]), 'Table'[Region] <> "West")) VAR SouthNotPresentCount = COUNTROWS(FILTER(ALL('Table'[Region]), 'Table'[Region] <> "South")) VAR EastNotPresentCount = COUNTROWS(FILTER(ALL('Table'[Region]), 'Table'[Region] <> "East")) RETURN SWITCH ( TRUE (), 'Table'[Region] = "North Not Present", NorthNotPresentCount,
'Table'[Region] = "West Not Present", WestNotPresentCount,
'Table'[Region] = "South Not Present", SouthNotPresentCount,
'Table'[Region] = "East Not Present", EastNotPresentCount )
Hi @Tito
Can you please try below DAX
MEASURE =
CALCULATE (
DISTINCTCOUNT ( 'YourTable'[Name] ),
ISBLANK ( 'YourTable'[Region] )
)
Let me know if that works for you
If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the Thumbs Up 👍 button if this comment helps you.
Thanks
Pijush
Linkedin
Hi @Tito
As your screenshot, there are no blank region
Please share your sample data in excel format, not as Screenshot
and expected result screenshot based on sample data
Hi @PijushRoy
I have 2 columns: Name and Region
|
|
Result:
Hi @Tito
Please find the PBIX file
https://drive.google.com/file/d/19hGo6zKDnV0GynZb39OcFlNo2_HCZ1RK/view?usp=sharing
Use the DAX for measure
MEASURE = VAR _rows = CALCULATE(COUNTROWS('Table'),ALL('Table'))
VAR _region = SELECTEDVALUE('Table'[Region])
RETURN
SWITCH(
TRUE(),
_region = "East", _rows - CALCULATE(COUNT('Table'[Region]),'Table'[Region]="East"),
_region = "North", _rows - CALCULATE(COUNT('Table'[Region]),'Table'[Region]="North"),
_region = "South", _rows - CALCULATE(COUNT('Table'[Region]),'Table'[Region]="South"),
_region = "West", _rows - CALCULATE(COUNT('Table'[Region]),'Table'[Region]="West"))
Let me know if that works for you
If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the Thumbs Up 👍 button if this comment helps you.
Thanks
Pijush
Linkedin
hi @PijushRoy
thank you very much! that also worked.
Column Region was just an example and we only have 4 variables, in the real Data are several variables and you can not write all in Measure manually.
If a name (A,B,C..) appears several times in the Name column, what should I change in Measure. Thank you!
Best regards
Tito
Hi @Tito
In my Measure, check the updated DAX
MEASURE = VAR _rows = CALCULATE(DISTINCTCOUNT('Table'[Name]),ALL('Table'))
VAR _region = SELECTEDVALUE('Table'[Region])
RETURN
SWITCH(
TRUE(),
_region = "East", _rows - CALCULATE(COUNT('Table'[Region]),'Table'[Region]="East"),
_region = "North", _rows - CALCULATE(COUNT('Table'[Region]),'Table'[Region]="North"),
_region = "South", _rows - CALCULATE(COUNT('Table'[Region]),'Table'[Region]="South"),
_region = "West", _rows - CALCULATE(COUNT('Table'[Region]),'Table'[Region]="West"))
In @Rohit11 Measure, check the updated DAX
Count Blanks =
var _totalRegion = CALCULATE(DISTINCTCOUNT('Table'[Name]),ALL('Table'))
return
(_totalRegion -
CALCULATE(COUNTROWS('Table'),VALUES('Table'[Region]) ))
A and H respected
Thanks, @Rohit11 for your measure
Let me know if that works for you
If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the Thumbs Up 👍 button if this comment helps you.
Thanks
Pijush
Linkedin
Thank you both. It worked great.
If it is possible, we can edit the measure so that we are no longer shown total -2. For example with Blank().
Best regards
Tito
Hi @Tito
Find the updated measure
Count Blanks =
var _totalRegion = CALCULATE(DISTINCTCOUNT('Table'[Name]),ALL('Table'))
return
IF(
HASONEVALUE('Table'[Region]),
(_totalRegion -
CALCULATE(COUNTROWS('Table'),VALUES('Table'[Region]) )),
BLANK())
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 |
---|---|
109 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |