Looking to create Only(Dimension Name) measure as done in Qlik same in PowerBI

[alexa_0028]

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

[AlB · Accepted 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.

[v-yiruan-msft · Accepted solution]

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

[AlB · Accepted 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.