topic Re: Conditional Color based on COUNT for Guage Visual and multiple tables in DAX Commands and Tips

Conditional Color based on COUNT for Guage Visual and multiple tables

Mmench — Mon, 19 Aug 2024 12:41:23 GMT

I'm trying to show a visual of authorized position for multiple locations vs actual.

RESULTS: RED gradient for 'under manned' and GREEN gradient for 'overmanned' and WHITE if authorized equals manned.

Table1: all manning data every name, their job, location, etc (a living document)

Columns: [NAME], [LOCATION], [JOBTITLE], etc

Table2: a set document (that a director might change later based on needs) showing location, jobtitle authorized and paygrade this table includes every one of the 20 job authorizations at each of the locations. So, I might have a total entry of 16 jobcode 3X3A across all locations, but 5 here and 3 there, etc.

Columns: [LOCATION], [JOBTITLE], [PAYGRADE]

Current page setup is a slicer to filter by location and 20 GUAGE visuals (newCard would also work if easier) that are each filtered by a COUNT of each of the twenty Table1 and Table2[JOBTITLE] jobs... 3X3A, 3X3B, etc. This gives me two numbers to play with... Authorized and Assigned.

I think the tables are linked right as I get the correct results, but...

I'd love to have the visual show RED gradient if Assigned is less than Authorized, WHITE if equal, and GREEN gradient if overmanned.

I've dabbled in DAX, but don't know enough to figure this out from slightly related examples. I've tried creating a new column that would do the count AND show me the difference between authorized and assigned... I'm just kinda stuck looking at a nice page I've created that is just all one color BLUE.

Any thoughts??

thx, -mm

Re: Conditional Color based on COUNT for Guage Visual and multiple tables

lbendlin — Sat, 17 Aug 2024 23:52:02 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Conditional Color based on COUNT for Guage Visual and multiple tables

Mmench — Mon, 19 Aug 2024 12:41:52 GMT

Thanks... hopefully this works.

I've only listed a few of the jobs on the page with all the gauges, so some of the locations will be blank.

Again, instead of BLUR I'd love to see RED for Assigned less than Authorized, White for equal to, and GREEN for Assigned greater than Authorized. If there was a way to make it a gradient based on % that would be super!

also... Any visual is ok if it works. I've used NewCards instead of guage here, too.

let me know if this doesn't work. Thanks again. -mm

Re: Conditional Color based on COUNT for Guage Visual and multiple tables

lbendlin — Sun, 18 Aug 2024 23:13:31 GMT

Your data model is not in a usable shape

which table is the dimension and which is the fact? You may need to have additional dimensions like Location.

Re: Conditional Color based on COUNT for Guage Visual and multiple tables

Mmench — Mon, 19 Aug 2024 10:22:59 GMT

Hey, thanks.

AUTHORIZATIONS table is the current allowable number of jobs (SPACES), while the MASTER DATA (FACES) is the positions manned by actual people. Not sure which would be defined as dimension or fact. Spaces vs Faces is the only way I know to describe it.

I think this is my whole problem. Maybe I'm in the wrong forum? I hate to say that I don't know what you are asking. Currently they are linked by LOCATION.

Maybe I need a new table for the formula, I really don't know what I need to do first to get what I want. I thought it could be as easy as creating a conditional.

ii hoped there was a formula that I could do something like...

= COUNT(tableA[columnX]-tableB[columnY]), "difference"

IF "difference"<0, "RED" .. >0, "BLUE" .. =0, "WHITE"

I do appreciate any help or pointing me in a good direction. Thx!

Re: Conditional Color based on COUNT for Guage Visual and multiple tables

lbendlin — Mon, 19 Aug 2024 12:15:54 GMT

Yes, you may want to invest some time into learning the basics of data modeling in Power BI. The Power BI Desktop App has a Help menu with great links to training material.

Re: Conditional Color based on COUNT for Guage Visual and multiple tables

Mmench — Mon, 19 Aug 2024 14:23:48 GMT

ANSWERED

Create a NewMeasure to find the difference and then use THAT as the field to base conditional formatting.

-mm