Can Excel visuals be replicated in power bi dax

Hi  Rkrama ,

Here are the steps you can follow：

1. Create calculated table.

Table 2 =
var _table1=
{"Assigned Count","Credentialed Count","Disable Count"}
var _table2=
DISTINCT('Table'[ConTracts])
RETURN
UNION(
    _table2,_table1)

2. Create measure.

Value =
var _column=SELECTCOLUMNS('Table',"1",'Table'[ConTracts])
return
SWITCH(
    TRUE(),
    MAX('Table 2'[ConTracts]) = "Assigned Count",
    COUNTX(
        FILTER(ALL('Table'),
        'Table'[Group]=MAX('Table'[Group])&&'Table'[Value]="A"),[Value]),
    MAX('Table 2'[ConTracts]) = "Credentialed Count",
    COUNTX(
        FILTER(ALL('Table'),
        'Table'[Group]=MAX('Table'[Group])&&'Table'[Value]="C"),[Value]),  
    MAX('Table 2'[ConTracts]) = "Disable Count",
    COUNTX(
        FILTER(ALL('Table'),
        'Table'[Group]=MAX('Table'[Group])&&'Table'[Value]="D"),[Value]),
    MAX('Table 2'[ConTracts]) in _column&& MAX('Table'[Value]) <> BLANK(),
MAXX(
    FILTER(ALL('Table'),
    'Table'[ConTracts]=MAX('Table 2'[ConTracts])&&'Table'[Group]=MAX('Table'[Group])),[Value])
,BLANK())

Color =
IF(
    ISERROR(VALUE([Value])) && [Value]<>BLANK(),"green",
    IF(
        ISERROR(VALUE([Value])),"yellow"))

3. [Value] – Conditional formatting – Background color.

4. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Hi Anonymous ,

This is the requiremnt i have and i wrote a measure to display the Status untill highligted

1. In each cell...
   1. An 'A' will be displayed if the staffer is assigned to the contract
      1. In ContractStaff the Assigned column will have a 1
   2. An 'C' will be displayed if the staffer is only credentialed (has contractstaff record but not assigned or disabled)
      1. A ContractStaff record will exist but Assigned and disabled are BOTH 0's
   3. A 'D' will be displayed if the staffer has a contractstaff record but has a disabled state
      1. In ContractStaff the disabled column will have a 1 
2. Subtotal rows: Bottom 5 rows will be frozen (always displayed) and contain...
   1. An 'Assigned Count' row
      1. Subtotal of each staffer's assigned count of contracts (displayed with filters applied)
   2. A 'Credentialed Count' row
      1. Subtotal of each staffer's credentialed count of contracts (displayed with filters applied)
   3. A 'Disabled Count' row
      1. Subtotal of each staffer's disabled count of contracts (displayed with filters applied)
   4. A 'Non-Credentialed, Valid License' row
   5. A 'Credentialed, Invalid License' row
3. Subtotal Column on right
   1. For each row show subtotals for each contract of...
      1. Total assigned users
      2. Total credentialed users
      3. Total disabled users
4. License Conditional Formatting/Highlighting
   1. For Non-Subtotal Rows...
      1. Highlight green if the respective user has the appropriate license for that contract
      2. Highlight yellow if the respective user lacks the appropriate license for that contract
      3. Highlight red if the respective user lacks the appropriate license for that contract and they have ANY ContractStaff record (regardless of assigned, disabled, credentialed).

Dax is have used is 

Status =
SWITCH (
    TRUE (),
    COUNTROWS(FILTER(ContractStaff, ContractStaff[Assigned] = TRUE())) > 0 && COUNTROWS(FILTER(ContractStaff, ContractStaff[Disabled] = FALSE())) > 0, "A",
    COUNTROWS(FILTER(ContractStaff, ContractStaff[Assigned] = FALSE())) > 0 && COUNTROWS(FILTER(ContractStaff, ContractStaff[Disabled] = FALSE())) > 0, "C",
    COUNTROWS(FILTER(ContractStaff, ContractStaff[Disabled] = TRUE())) > 0, "D",
    BLANK()
)

I need to display the count of total A,C,D in the same matrix regards to the Staffname your help will be much appricated.thanks