cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Can _var measure with text be used in visualizations?

I have a Measure that uses multiple _var functions that ultimatley result in a specific "word" or "text phrase". In other words, after I type Result, I follow it with IF( _var=1, "Error" , "No Error"). When dragging the Measure into my Table, I can see the Errors and No Errors across thousands of rows.  I simply want to count the number of times "Error" appears and show it in a bar graph so I can show improvement over time. Is this possible? 

5 REPLIES 5
Highlighted
Super User IV
Super User IV

Re: Can _var measure with text be used in visualizations?

@Jacob111 ,Can you share sample data and sample output.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User II
Super User II

Re: Can _var measure with text be used in visualizations?

Hi @Jacob111 

try this

Count Error Measure = CALCULATE(COUNTROWS(Table), FILTER(ALL(Table), [YourMeasure] = "Error"))

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Highlighted
Helper I
Helper I

Re: Can _var measure with text be used in visualizations?

@amitchandak  Below is some sample data. Pricing Unit Error is the Measure and can be seen above the table. If you can streamline this formula to make it less CPU heavy, that would be fantastic!

 

@az38 You solution might work, however the count function is running off of the below formula and takes up way too much CPU to calculate.

 

0 Pricing Unit Error = 
var _ID = SELECTEDVALUE('Table'[Unique (Contract, Material, Item)])
var _Ifhaserror = 
CALCULATE(
    DISTINCTCOUNT('Table'[Pricing Unit]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Unique (Contract, Material, Item)] = _ID
    )
)

var _start = 
CALCULATE(
    SUM('Table'[Pricing Unit]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Unique (Contract, Material, Item)]=_ID&&
        'Table'[Contract End]  = 
        CALCULATE(
            MIN('Table'[Contract End]),
            FILTER(
                ALL('Table'),
                'Table'[Unique (Contract, Material, Item)] = _ID
            )
        )
    )
)

var _end = 
CALCULATE(
    SUM('Table'[Pricing Unit]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Unique (Contract, Material, Item)]=_ID&&
        'Table'[Contract End] = 
        CALCULATE(
            MAX('Table'[Contract End]),
            FILTER(
                ALL('Table'),
                'Table'[Unique (Contract, Material, Item)] = _ID
            )
        )
    )
)

return
IF(
    _Ifhaserror=1,
    "No Error",
    IF(
        _Ifhaserror>1&&_start=_end,
        "This Error has been Corrected",
        IF(
            _Ifhaserror>1&&_start<>_end,
            "Error"
        )
    )
)

 

 

 

Unique (Contract, Material, Item)Contract #MaterialItemPricing UnitContract BeginContract EndPricing Unit Error
45643 5 1045643510100011/2/20182/3/2019No Error
45643 5 104564351010002/4/201912/31/2999No Error
54353 4 1054353410100010/10/20191/5/2020No Error
54353 4 105435341010001/6/202012/31/2999No Error
54353 7 2054353720100010/10/20191/5/2020No Error
54353 7 205435372010001/6/202012/31/2999No Error
23455 5 1023455510110/18/201912/31/2999No Error
23455 2 2023455220110/18/201912/31/2999No Error
43543 7 1043543710100010/18/20193/5/2020No Error
43543 7 104354371010003/6/20203/31/2020No Error
43543 7 104354371010004/1/202012/31/2999No Error
2435 5 102435510111/1/20191/6/2020Error
2435 5 10243551010001/7/202012/31/2999Error
4564 8 104564810111/1/20191/29/2020Error
4564 8 10456481011/30/20203/22/2020Error
4564 8 10456481010003/23/202012/31/2999Error
4564 1 204564120111/1/20191/29/2020Error
4564 1 20456412011/30/20203/9/2020Error
4564 1 20456412013/10/20203/22/2020Error
4564 1 20456412010003/23/202012/31/2999Error
765456 9 2076545692011/1/20201/9/2020This Error has been Corrected
765456 9 20765456920100011/4/201912/31/2019This Error has been Corrected
765456 9 2076545692010001/10/20203/4/2020This Error has been Corrected
765456 9 2076545692010003/5/20203/5/2020This Error has been Corrected
765456 9 2076545692010003/6/202012/31/2999

This Error has been Corrected

 

 

Highlighted
Community Support
Community Support

Re: Can _var measure with text be used in visualizations?

Hi @Jacob111 ,

 

This looks like a measure totals problem. Very common. Below is a similar thread about it:
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
 
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
 
 
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
 
 
Highlighted
Community Support
Community Support

Re: Can _var measure with text be used in visualizations?

Hi @Jacob111

 

Is your issue solved?

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

Helpful resources

Announcements
June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors