Multiple dates issue in single graph

[Rikin-Nayak-007]

How to use DAX to create a relationship on a specific graph with an inactive date after overriding the effects of an active relationship date on the same graph.

I have item names on the x axis of the bar chart, and total items sold in a specific year on the y axis. 

For example :

CALCULATE(Master["Items"],ALL(Master[Purchase_date]),USERELATIONSHIP('Date'[Date],Master[Selling_date]))

This should show all items on the x axis that sold in a particular year while ignoring the purchase year. 

[TomMartens]

Hey @Rikin-Nayak-007 ,

please provide a pbix file that contains sample data, but still reflects your data model.

You can not create a measure that returns a table.
A measure can only return numeric values, a scalar value. It is possible to leverage virtual tables during the calculation of a measure, though.

I'm also wondering about your expectation if option1 is selected from the 2nd slicer, are there two bars for one item?

Please share the pbix and also the DAX measure used to represent the numeric values.
If you are talking about bars, horizontal rectangles where the width is used to represent the numeric values, if you are talking about columns, then the height of a vertical rectangle is used for data visualization.

Regards,

Tom

[Rikin-Nayak-007]

Here Why Item 4 Not appear in column chart ? My sampe data is 

And for column chart I used following expression for x - axis value:

Item_DAX =

Var slicer2 = SELECTEDVALUE(Sheet1[Status as on 2020])

Var Items_Dax =  IF(slicer2="Selling",CALCULATETABLE(DISTINCT(Sheet1[Item]),ALL(Sheet1[Purchase Date]),USERELATIONSHIP(Calender[Date],Sheet1[Selling Date])),CALCULATETABLE(DISTINCT(Sheet1[Item])))

return Items_Dax

[TomMartens]

Hey @Rikin-Nayak-007 ,

please  provide a pbix that contains sample data, but still reflects your data model, tables, relationships, calculated columns and measures.

You measure Item_DAX does not return a scalar value.

Next you need a table dimension table, that contains only the items.
Based on the data distribution item 4 can not return if you only have the sales table. I recommend reading this article: Understanding DAX Auto-Exist - SQLBI

Regards,
Tom

[Rikin-Nayak-007]

Let me ask my question another way:

Two slicers is there, and I want to update my bar chart based on the slicers' selection. (Items vs No of item sale in selected time)

2020 is selected as the slicer year.

There are two options in the second slice: 1. "selling and purchasing" 2. "selling only"

If the user selects "Selling only", all items that are selling in 2020 should be displayed. But here, due to the relationship table, it only displays those items that are purchased in 2020 and sold in 2020. I want to remove the active relation of the purchase date if selling is selected on slicer.

I tried following dax, but it is not giving the required output.

Var slicer2 = SELECTEDVALUE(Master[Status])
Var Items= IF(slicer2="Selling only",CALCULATETABLE(DISTINCT(Master[Items]),ALL(Master[Purchase_date]),USERELATIONSHIP('Date'[Date],Master[Selling_Date])),CALCULATETABLE(DISTINCT(Master[Items])))

return Items

Here Two dates are there in master file :

purchase date : In Active relation with calender table and

selling date : In Inactive relation with calender table.

[Rikin-Nayak-007 · Accepted solution]

I found the solution as make all relations with Calender table inactive. Wherever necessary, construct active relations using the DAX formula USERELATIONSHIP.

[TomMartens]

Hey @Rikin-Nayak-007 ,

I have to admit that I do not understand your requirement.

I assume that there is a slicer that filters the year ('date'[date]), as no date is involved on the chart.

I also do not understand the measure you provided, as it does not contain an aggregate function for the first parameter, the numeric expression. I think it has to look like this:

CALCULATE(
    COUNT( 'master'[item] )
    , ...
)

Maybe it should look like this to calculate the sales value with the change of the relationship:

CALCULATE(
    SUM( '<the name of the sales fact table>'[<sales valuue>] )

    ,USERELATIONSHIP('Date'[Date],Master[Selling_date]))

)

But as I mentioned, I do not exactly understand your requirement.
Consider creating a pbix that contains sample data but still reflects your data model (tables, relationships, calculated columns, and measures). Upload the pbix file to onedrive, google drive, or dropbox and share the link. If you are using Excel to create the sample data instead of the manual input method, share the Excel file.
Please elaborate on the problem you are facing based on your sample data.

Regards, 

Tom