How to count user input along with blank field od date column for different periods

saivina2920 

You can create a measure with below codes to display the new relieve date in the table visual.

new_RELIEVE_DATE = 
IF (
    ISINSCOPE ( EMP_TABLE[EMP_NO] ),
    SWITCH (
        ISBLANK ( Parameter[Parameter Value] ),
        TRUE (),
            IF (
                ISBLANK ( SELECTEDVALUE ( EMP_TABLE[EMP_RELIEVE_DATE] ) ),
                BLANK (),
                SELECTEDVALUE ( EMP_TABLE[EMP_RELIEVE_DATE] )
            ),
        FALSE (),
            IF (
                ISBLANK ( SELECTEDVALUE ( EMP_TABLE[EMP_RELIEVE_DATE] ) ),
                SELECTEDVALUE ( EMP_TABLE[EMP_RESIGN_DATE] ) + Parameter[Parameter Value],
                SELECTEDVALUE ( EMP_TABLE[EMP_RELIEVE_DATE] )
            )
    ),
    BLANK ()
)

And I add two filters to the table visual. You can hide them if you don't want report users to change these filters.

[Relieve date flag] measure is used to determine whether the new relieve date is in the future or blank or in the past. If it is in the future or blank, the measure will return 0 and these rows will not display in the visual. 

Relieve date flag = IF([new_RELIEVE_DATE]>[vToday] || ISBLANK([new_RELIEVE_DATE]),0,1)

For details, please download the attachment. Let me know if you have any questions.

Regards,

Jing

Yes. you are perfect. Everything is good except one are two.

Table value is coming perfect. but, chart and multi-row card count mismatching without user input.

below screenshot for your reference.

The other part, with user input all the values (Table,Chart and Multi-Row Card) coming perfect. below screenshot for your reference.

Where we need to change first screenshot..

pls. let us know if you need anymore details. almost we are in finishing stage.

can you pls. give us the update when you have time...?

Hi saivina2920 

I think the cause is probably the boundary dates.

In [<10Days], it filters dates which are >= [vCalDate10]. While in [10-20Days], it filters dates which are >= [vCalDate20] and <= [vCalDate10]. If a date is happenly on [vCalDate10], it will be counted both in [<10Days] and [10-20Days]. The same happens on [vCalDate20] in the [10-20 days] and [> 20 days] measures. 

Only remain one equal sign on the boundry dates and test the measures with some boundry date values. 

I have only two records for the selection "Alaska". then how it will come 3 reocrds in pie chart an multi-row card. The correct count will reflect in Table chart.

Can you pls. give us more clarity on this..?

Sorry, I find the cause is the new column added in the table variable in measures. The logic I dealt with the new relieve date when parameter value is blank was wrong. I gave it the resign date. Am trying to modify the codes.

saivina2920 Please download the attachment and see the page 2 tab. Although boundary date is not the cause, I remain only one equal sign in new measures. You can change that per your need.

How did you move all measures to one group measures...?

You can create a table by Enter data with any value, then move the measures to the table by changing their Home table. After that, delete the column in this table, close and reopen the pbix file.

Other reference: https://radacad.com/organize-power-bi-dax-measures-in-folders

BTW, are the new measures working?

Thanks. new measures working are working except some minor bug.

1. "new_RELIEVE_DATE" showing some invalid date like "19/01/1900". But, "EMP_RESIGN_DATE" data is there. (This will be happening after enter the input). 

The count is coming perfect,but, displaying the date improper.

Suppose if i add "EMP_RESIGN_DATE" field in table chart, then date is coming. remaining filter is same. below screenshot for reference.

2. By default, Chart (Pie Chart and Table Chart) count should be blank until or unless i am selecting "EMP_STATE" slicer input.

for example, if i select "EMP_STATE" = "Alaska" then only i should display the count in chart.

otherwise, table chart should be blank.

IF the above solved, mine is over.

pls. find the PBIX and test file in below url.

https://1drv.ms/u/s!AiSRcgO5FUmN8Spap0qCDsxGfe-b?e=GDZc7H

Thanks for your great support and followup.

pls. let us know, if you need more details and look at this when you have time. 

Any update pls.

Hi saivina2920 

I solve the second one, but I'm not able to solve the first one. See the attachment.

The "new_RELIEVE_DATE" is a measure. A measure is calculated according to the context it is located in and a measure only returns an aggregate value. In this measure, it needs a specific "EMP_RESIGN_DATE" value to calcualte the correct new relieve date. And I use SELECTEDVALUE() to get the resign date. The SELECTEDVALUE() function will return blank if it gets more than one distinct value or blank value. 

For example, if you don't put "EMP_RESIGN_DATE" column into the table visual, EMP-610 with working status in Alaska will have two resign dates, so SELECTEDVALUE(EMP_TABLE[EMP_RESIGN_DATE]) will return blank and the new relieve date will be blank+input. However, because input is not blank and the measure should return a date value, it will pick Power BI's default start date 1899-12-30 for the blank date to calculate. 

So I suggest adding "EMP_RESIGN_DATE" column into the table visual to provide a more specific context for "new_RELIEVE_DATE" measure.

Regards,
Jing
If this post helps, please Accept it as the solution to help other members find it.

test

Thanks for your reply..

We have created measure for more filter and applying in that. Working fine.

Flag 2 = IF(ISFILTERED(EMP_TABLE[EMP_STATE]),1,0)

like that i have created more flags and i want to maintain those flags permenantly.

But, when clear all filter, then the above measure (flag 2,flag 3,flag 4,...etc) filter also cleared. this, I want to retain permenantly. otherwise, end user will get more confusion.

then only, i can get the correct count.

If this is done, then my all the issues are over..

pls. help.

 

 

test

pls. update when you have time. Thanks for all your support.

pls. update the same.

Any update pls.

Finally we achieved. You are rocking. Thanks for all your smile and favourable replies.

One last question.

I have tested all the cases. some places displaying Future Date also. we should truncate in the chart table.

Count has not displaying, but, date will be displaying.

how to avoid to display future date in new_Relieve_Date at chart table

I am facing another issue and which i posted in another thread.

yes. i am not able to get today records in <5 days period. Why..

https://community.powerbi.com/t5/Desktop/Data-not-fetching-for-TODAY-DATE-in-DAX-measure/m-p/1764197#M692011

vToday = TODAY()
vCalDate5 = [vToday] - 5
Emp Joined Date =
CALCULATE(DISTINCTCOUNT(EMP_TABLE[EMP_NUMBER]),FILTER(EMP_TABLE,
(EMP_TABLE[JOINED_DATE] >= [vCalDate5] && EMP_TABLE[JOINED_DATE] <= [vToday]

&& EMP_STATUS = "ACTIVE")

Hi saivina2920 

I remember there is a [Relieve date flag] measure in my pbix file which deals with this situation. You can modify it or create a new measure similar to it, and apply this measure as a filter on the table visual. I have description about this measure in my reply on 3/31/2021.