topic Re: Mesure and Calendar table in DAX Commands and Tips

Mesure and Calendar table

dofrancis3 — Thu, 08 Feb 2024 22:24:16 GMT

Hi Colleagues!

I've created a "Calendar" table, but when I try to create a measure, "Calenda table" doesn't appear.

Could you help me to fixe this issue please.

In other words, when I call up "Calenda table" to create a measure, the table is not visible.
Kindly, advise.

Please see the print screen below:

Re: Mesure and Calendar table

wynhopkins — Thu, 08 Feb 2024 22:32:30 GMT

You can't directly reference a table or column in a measure. It's got to be wrapped in a function like a SUM, COUNTROWS, CALCULATE.

what are you trying to do with the measure

Re: Mesure and Calendar table

dofrancis3 — Thu, 08 Feb 2024 23:21:32 GMT

I would like to calculate the pourcentage of status without "blank dates" and pourcentage "include blank dates"
below is my Mesure:

% Status = DIVIDE([NbrPass], [NbrStatus], 'Calendar'[Date] = BLANK())

Knowing that:

NbrPass = CALCULATE(COUNTROWS(repository),repository[STATUS]= "Pass")

NbrStatus = CALCULATE(COUNTROWS(repository),repository[STATUS]= "Pass"||repository[STATUS]= "Fail")

Re: Mesure and Calendar table

wynhopkins — Fri, 09 Feb 2024 00:25:10 GMT

Your Calendar table shouldn't have blank dates so don't reference that.

Number of records = COUNTROWS(repository)
Q: is there status other than pass or fail?

Blank Dates = CALCULATE( COUNTROWS(repository), ISBLANK(repository[Date Column]) )

Replace Date Column with whichever column you're checkin in the repository status

% = DIVIDE( [Blank Dates, [Number of records])

Re: Mesure and Calendar table

dofrancis3 — Sat, 10 Feb 2024 13:01:48 GMT

Dear Coleagues;

I would like to Summarize "sampled" colomn only if the "date" is not blank

Be low my mesure

Tot_sampled =

VAR __numberOfInterwd = SUM(LQASrepository[Total sampled),

&& LQASrepository[Start date] <> BLANK ())

RETURN

IF(ISBLANK(__numberOfInterwd), "No Data",__numberOfInterwd)

Please support

Re: Mesure and Calendar table

wynhopkins — Sun, 11 Feb 2024 05:10:00 GMT

I don't understand your question sorry

Re: Mesure and Calendar table

talespin — Sun, 11 Feb 2024 06:40:51 GMT

hi dofrancis3 ,

This is what I used

----------------------------------------------------------------------------------------

Calendar table :

CALENDAR = CALENDAR("2023-01-01", "2024-12-31")

----------------------------------------------------------------------------------------

% Status without Blank Date =

VAR _TotalCount = CALCULATE( COUNT(FactTable[ID]), KEEPFILTERS(NOT(ISBLANK('CALENDAR'[Date]))) )

VAR _PassCount = CALCULATE( COUNT(FactTable[ID]), FactTable[Status] = "Pass" , KEEPFILTERS(NOT(ISBLANK('CALENDAR'[Date]))) )

RETURN DIVIDE( _PassCount, _TotalCount)

----------------------------------------------------------------------------------------

% Status with Blank Date =

VAR _SelYear = SELECTEDVALUE('CALENDAR'[YEAR])

VAR _TotalCount = CALCULATE( COUNT(FactTable[ID]) , REMOVEFILTERS('CALENDAR'[Date]), 'CALENDAR'[YEAR] = _SelYear || ISBLANK('CALENDAR'[YEAR]) )

VAR _PassCount = CALCULATE( COUNT(FactTable[ID]), FactTable[Status] = "Pass" , REMOVEFILTERS('CALENDAR'[Date]), 'CALENDAR'[YEAR] = _SelYear || ISBLANK('CALENDAR'[YEAR]))

RETURN DIVIDE( _PassCount, _TotalCount)

----------------------------------------------------------------------------------------