cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
twentyone Regular Visitor
Regular Visitor

Condition to return text value

Hello!

 

I have the following tables:

 

Tab 1: Dayparts

Daypart               Slot Start    Slot End

Early morning       07:00          09:59

Morning               10:00          12:59

......

 

Tab 2: Programmes

Programme          Slot mid point              Daypart

News                          11:35                    {DAX formula}

 

What I want to is to get a new column in the Programmes table that returns the value in Daypart if the Slot Mid Point falls between Slot Start and Slot End.

 

I would use Calculate if Daypart contained numbers, but what formula do I need to use so that I can get the right value from Dayparts/Daypart using the time conditions?

 

Any help much appreciated!

 

Many thanks,

 

George.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Condition to return text value

Hi George,

 

Have a look at Static Segmentation on DAX Patterns:

http://www.daxpatterns.com/static-segmentation/

 

As long as your times have Data Type Time, you should be able to use this approach.

 

Owen Smiley Happy



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Super User
Super User

Re: Condition to return text value

Hi George@twentyone

 

Just making sure - you should use the VALUES function (not the VALUE function) in that pattern.

 

VALUES ( Dayparts[Daypart] ) is used to return a single value from the Dayparts[Daypart] column corresponding to each row of Programmes.

 

(The VALUE function is used to convert text to numbers).

 

The Dayparts table should not be related to Programmes for Static Segmentation.

 

Here is a dummy pbix file illustrating how you could create a Static Segmentation calculated column using the tables in your example.

https://www.dropbox.com/s/ike0q13pxhr3oca/Static%20segmentation%20time.pbix?dl=1

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




6 REPLIES 6
Super User
Super User

Re: Condition to return text value

Hi George,

 

Have a look at Static Segmentation on DAX Patterns:

http://www.daxpatterns.com/static-segmentation/

 

As long as your times have Data Type Time, you should be able to use this approach.

 

Owen Smiley Happy



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




twentyone Regular Visitor
Regular Visitor

Re: Condition to return text value

Hi Owen,

 

Thank you for your response.

 

I had tried this but the problem I'm facing is that DAX will not allow me to draw the Daypart value from the Dayparts table (when I type in the Programmes Tab 

Daypart =  Calculate (Value(

 

it doesn't give me the option to pick 'Dayparts'[Daypart], nor does Related work after Value(. 

NB I have linked the two tables on Slot Start -> Slot Mid Point.

 

Any suggestions on how DAX can pick up the column from Dayparts?

 

Many thanks,

 

George. 

Super User
Super User

Re: Condition to return text value

Hi There,

 

You need to create a index column at the query stage in both the tables starting from 1.

 

Create a relationships between the tables based on this column.

 

and then

1. Create a Calculated column in Table 2

Slot Ending = IF(Table2[Slot Mid Point]<RELATED(Table1[Slot End]),"True","False")

2. Create another calculated column in Table 2

DayPart = IF(Table2[Slot Ending]="False","NA",RELATED(Table1[Daypart])).

 

This will give you the results expected.

 

Please refer to the screenshots for more information.

 

scr1.PNGscr2.PNGscr3.PNG

 

Thanks & Regards,

Bhavesh

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
Super User
Super User

Re: Condition to return text value

Hi George@twentyone

 

Just making sure - you should use the VALUES function (not the VALUE function) in that pattern.

 

VALUES ( Dayparts[Daypart] ) is used to return a single value from the Dayparts[Daypart] column corresponding to each row of Programmes.

 

(The VALUE function is used to convert text to numbers).

 

The Dayparts table should not be related to Programmes for Static Segmentation.

 

Here is a dummy pbix file illustrating how you could create a Static Segmentation calculated column using the tables in your example.

https://www.dropbox.com/s/ike0q13pxhr3oca/Static%20segmentation%20time.pbix?dl=1

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




twentyone Regular Visitor
Regular Visitor

Re: Condition to return text value

Thanks a million for all your help! This did the trick.

 

I also had to fix the time format in the Advanced Editor to "time" rather than "datetime", which caused the two columns not to match.

 

George.

RajivDixit Frequent Visitor
Frequent Visitor

Re: Condition to return text value

 Hi @OwenAuger

 

I am in urgernt need and request your help in following scenario

 

I have a mapping of Cluster and LOCs and want to display LOCs in a PBI Matrix object with different KPIs for the filtered Cluster in slicer.

 

Cluster1   LOC1

Cluster1   LOC2

Cluster1   LOC3

Cluster1   LOC4

 

Cluster2   LOC1

Cluster2   LOC2

 

Cluster3   LOC3

Cluster3   LOC4

 

My requirement is if no Cluster is filtered in slicer then it should show the list of LOCs for "Cluster1" only