Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
alexa_0028
Resolver II
Resolver II

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

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 CategoryAreaTierMeasure Value
DefaultSalesArea 1A8
DefaultSalesArea 1A5
DefaultSalesArea 2A18
DefaultSalesArea 1A3
DefaultSalesArea 2A10
DefaultSalesArea 3A30
DefaultSalesArea 1A6
DefaultSalesArea 1A2
DefaultSalesArea 1B0
DefaultSalesArea 1B0
DefaultSalesArea 2B0

 

Goal :

Type CategoryAreaTierValue
DefaultSalesArea 1ANull
DefaultSalesArea 1BNull
DefaultSalesArea 2ANull
DefaultSalesArea 2B0
DefaultSalesArea 3A30



Please help me here


2 ACCEPTED SOLUTIONS
AlB
Super User
Super User

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

 

SU18_powerbi_badge

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.

 

 

 

View solution in original post

v-yiruan-msft
Community Support
Community Support

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 )

yingyinr_0-1633074412321.png

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yiruan-msft
Community Support
Community Support

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 )

yingyinr_0-1633074412321.png

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
AlB
Super User
Super User

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

 

SU18_powerbi_badge

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.

 

 

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.