Clustered column chart and DAX query help

[Anonymous]

Hi experts,

Need help with clustered column chart and Dax formula

Not sure if I’m doing the right way

I have three tables

Period table, Date table, patient table

Period table has fields period date and sort columns

Sample data is like

Period	Date	Sort
Current Month To Date	Friday, 3 July 2020	3
Current Month To Date	Thursday, 2 July 2020	3
Current Month To Date	Wednesday, 1 July 2020	3
Current Week To Date	Friday, 3 July 2020	2
Current Week To Date	Thursday, 2 July 2020	2
Current Week To Date	Wednesday, 1 July 2020	2
Current Week To Date	Tuesday, 30 June 2020	2
Current Week To Date	Monday, 29 June 2020	2
Today	Friday, 3 July 2020	1

Date table contains all dates-example

date	year	month	Year/month	quarter	Year/quarter	dayofmonth
Saturday,4 july 2020	2020	07	2020/07	Q3	2020/q3	4

patient table contains

Admissionid	patientnumber	dateofbirth	name	admisiondate	dischargedate	nofodays	code	status
121166	101	01/01/1980	Kaz	Wednesday,10 june 2020	Fridayday,13june 2020	3	ABC	Coded
123617	101	01/01/1980	Kaz	friday,19june 2020	monday,22june 2020	4	CBE	Coded
124687	101	01/01/1980	kaz	Wednesday,1july 2020	Thursday,2july 2020	2		notcoded
121167	102	01/01/1981	Tod	Wednesday,10 june 2020	Fridayday,13june 2020	3	ABC	Coded
123618	102	01/01/1981	Tod	friday,19june 2020	monday,22june 2020	4	CBE	Coded
124688	104	01/01/1980	rez	Wednesday,1july 2020	Thursday,2july 2020	2		notcoded

Relationships to table

Date to period – 1 to * (single)

Date to patient – 1 to *(single)

My requirement is to present in a chart

By slicer period table

Today,

Current week to date,

Current month to date,

Current year to date

could you please suggest or give a solution?

thanks in advance

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Patient:

Date(a calculated table):

Date = CALENDAR(DATE(2020,1,1),DATE(2020,12,31))

Period:

There is no realtionship between 'Period' and 'Patient'. You may create calculated columns and measures as below.

Calculated column:

Current Week To Date = 
IF(
    'Date'[Year]=YEAR(TODAY())&&'Date'[Weeknum]=WEEKNUM(TODAY())&&'Date'[Date]<=TODAY(),
    1,0
)

Current Month To Date = 
IF(
        'Date'[Year]=YEAR(TODAY())&&'Date'[Month]=MONTH(TODAY())&&'Date'[Date]<=TODAY(),
        1,0
)

Current Year to Date = 
IF(
            'Date'[Year]=YEAR(TODAY())&&'Date'[Date]<=TODAY(),
            1,0
)

Measure:

Visual Control = 
var _period = SELECTEDVALUE('Period'[Period])
var _tab =
ADDCOLUMNS(
    Patient,
    "min",
    SWITCH(
        _period,
        "Current Week to Date",MINX(FILTER(ALL('Date'),'Date'[Current Week To Date]=1),'Date'[Date]),
        "Current Month to Date",MINX(FILTER(ALL('Date'),'Date'[Current Month To Date]=1),'Date'[Date]),
        "Current Year to Date",MINX(FILTER(ALL('Date'),'Date'[Current Year to Date]=1),'Date'[Date]),
        BLANK()
    ),
    "max",
    SWITCH(
        _period,
        "Current Week to Date",MAXX(FILTER(ALL('Date'),'Date'[Current Week To Date]=1),'Date'[Date]),
        "Current Month to Date",MAXX(FILTER(ALL('Date'),'Date'[Current Month To Date]=1),'Date'[Date]),
        "Current Year to Date",MAXX(FILTER(ALL('Date'),'Date'[Current Year to Date]=1),'Date'[Date]),
        BLANK()
    )
)
var newtab=
ADDCOLUMNS(
    _tab,
    "flag",
    IF(
        NOT(
            OR(
                [admisiondate]>[max],
                [dischargedate]<[min]
            )
        ),
        1,0
    )
)
return
IF(
    ISFILTERED(Period[Period]),
    SUMX(
        newtab,
        [flag]
    ),
    1
)

Then you need to put the measure in the visual level filter and use 'Period' column from 'Period' table to filter out the result. Today is 7/6/2020.

Best Regards

Allan

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

[parry2k]

@Anonymous make sure cross filter direction is set to both for the relationship between period table and date table, or in measures, make sure you are using CROSSFILTER DAX function and 3rd parameter value set to BOTH.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡