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.
Hi All,
I am having a requirement like I wanted to create a measure like Only(dimension name) in power bi.
So the only measure returns nulls if there is more than one value of dimension present over the aggregated data (table) or returns the value itself if there is only one value.
Example :
Given :
Type | Category | Area | Tier | Measure Value |
Default | Sales | Area 1 | A | 8 |
Default | Sales | Area 1 | A | 5 |
Default | Sales | Area 2 | A | 18 |
Default | Sales | Area 1 | A | 3 |
Default | Sales | Area 2 | A | 10 |
Default | Sales | Area 3 | A | 30 |
Default | Sales | Area 1 | A | 6 |
Default | Sales | Area 1 | A | 2 |
Default | Sales | Area 1 | B | 0 |
Default | Sales | Area 1 | B | 0 |
Default | Sales | Area 2 | B | 0 |
Goal :
Type | Category | Area | Tier | Value |
Default | Sales | Area 1 | A | Null |
Default | Sales | Area 1 | B | Null |
Default | Sales | Area 2 | A | Null |
Default | Sales | Area 2 | B | 0 |
Default | Sales | Area 3 | A | 30 |
Please help me here
Solved! Go to Solution.
Hi @alexa_0028
1. Place Type, Category, Area, Tier in a table visual
2. Place this measure in the table
Value =
VAR aux_ = COUNTROWS(Table1)
RETURN
IF(aux_ <= 1, SELECTEDVALUE(Table1[Measure]))
3. Select to show blanks if necessary
See it at work in the attached file
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
Hi @alexa_0028 ,
Assuming that [Measure Value] is the actual field in the table, you can create a measure as below, please find the details in the attachment.
Value =
VAR _curtype = SELECTEDVALUE ( 'Table'[Type] )
VAR _curcategory = SELECTEDVALUE ( 'Table'[Category] )
VAR _curarea = SELECTEDVALUE ( 'Table'[Area] )
VAR _curtier = SELECTEDVALUE ( 'Table'[tier] )
VAR _count =
COUNTX (
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Type] = _curtype
&& 'Table'[Category] = _curcategory
&& 'Table'[Area] = _curarea
&& 'Table'[Tier] = _curtier
),
[Measure Value]
)
VAR _mvalue =
CALCULATE (
SUM ( 'Table'[Measure Value] ),
FILTER (
'Table',
'Table'[Type] = _curtype
&& 'Table'[Category] = _curcategory
&& 'Table'[Area] = _curarea
&& 'Table'[Tier] = _curtier
)
)
RETURN
IF ( _count > 1, BLANK (), _mvalue )
If the above one is not working, please provide more sample data and its formula if [Measure Value] is a measure. Thank you.
Best Regards
Hi @alexa_0028 ,
Assuming that [Measure Value] is the actual field in the table, you can create a measure as below, please find the details in the attachment.
Value =
VAR _curtype = SELECTEDVALUE ( 'Table'[Type] )
VAR _curcategory = SELECTEDVALUE ( 'Table'[Category] )
VAR _curarea = SELECTEDVALUE ( 'Table'[Area] )
VAR _curtier = SELECTEDVALUE ( 'Table'[tier] )
VAR _count =
COUNTX (
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Type] = _curtype
&& 'Table'[Category] = _curcategory
&& 'Table'[Area] = _curarea
&& 'Table'[Tier] = _curtier
),
[Measure Value]
)
VAR _mvalue =
CALCULATE (
SUM ( 'Table'[Measure Value] ),
FILTER (
'Table',
'Table'[Type] = _curtype
&& 'Table'[Category] = _curcategory
&& 'Table'[Area] = _curarea
&& 'Table'[Tier] = _curtier
)
)
RETURN
IF ( _count > 1, BLANK (), _mvalue )
If the above one is not working, please provide more sample data and its formula if [Measure Value] is a measure. Thank you.
Best Regards
Hi @alexa_0028
1. Place Type, Category, Area, Tier in a table visual
2. Place this measure in the table
Value =
VAR aux_ = COUNTROWS(Table1)
RETURN
IF(aux_ <= 1, SELECTEDVALUE(Table1[Measure]))
3. Select to show blanks if necessary
See it at work in the attached file
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
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 |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |