Forum Discussion

GalaBI79's avatar
GalaBI79
Regular Visitor
1 year ago
Solved

Slicer selection not working in virtual table creation

Hi all,   First of all, apologies for my english, but it's not my first language 😁   I'm new to Power BI so sorry for any mistakes.   I have a table with invoices, organized by invoice date. I...
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi GalaBI79 ,

     

    I made a sample for you.

    Measure = 
var _column = MAX('Table3'[Column1])
var _value= SWITCH(TRUE(),
_column="Today"&&HASONEVALUE('Calendar'[Date]),CALCULATE([InvoiceValue],'Invoice'[InvoiceDate]=SELECTEDVALUE('Calendar'[Date])),
_column="Month"&&HASONEVALUE('Calendar'[Date]),CALCULATE([InvoiceValue],FORMAT('Invoice'[InvoiceDate],"MMMM")=SELECTEDVALUE('Calendar'[Date].[Month])),
_column="Month To Date"&&HASONEVALUE('Calendar'[Date]),CALCULATE([InvoiceValue],Month('Invoice'[InvoiceDate])<=MONTH(SELECTEDVALUE('Calendar'[Date]))),
_column="Today"&&HASONEVALUE('Calendar'[Date].[Month]),CALCULATE([InvoiceValue],'Invoice'[InvoiceDate]=MAX('Calendar'[Date])),
_column="Month"&&HASONEVALUE('Calendar'[Date].[Month]),CALCULATE([InvoiceValue],FORMAT('Invoice'[InvoiceDate],"MMMM")=SELECTEDVALUE('Calendar'[Date].[Month])),
_column="Month To Date"&&HASONEVALUE('Calendar'[Date].[Month]),CALCULATE([InvoiceValue],Month('Invoice'[InvoiceDate])<=MONTH(MIN('Calendar'[Date])))
)
RETURN _value

    Note: This measure only considers the month and does not consider the year as a filter condition.

     

    Best Regards,

    Wearsky

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi GalaBI79 ,

 

I made a sample for you.

Measure = 
var _column = MAX('Table3'[Column1])
var _value= SWITCH(TRUE(),
_column="Today"&&HASONEVALUE('Calendar'[Date]),CALCULATE([InvoiceValue],'Invoice'[InvoiceDate]=SELECTEDVALUE('Calendar'[Date])),
_column="Month"&&HASONEVALUE('Calendar'[Date]),CALCULATE([InvoiceValue],FORMAT('Invoice'[InvoiceDate],"MMMM")=SELECTEDVALUE('Calendar'[Date].[Month])),
_column="Month To Date"&&HASONEVALUE('Calendar'[Date]),CALCULATE([InvoiceValue],Month('Invoice'[InvoiceDate])<=MONTH(SELECTEDVALUE('Calendar'[Date]))),
_column="Today"&&HASONEVALUE('Calendar'[Date].[Month]),CALCULATE([InvoiceValue],'Invoice'[InvoiceDate]=MAX('Calendar'[Date])),
_column="Month"&&HASONEVALUE('Calendar'[Date].[Month]),CALCULATE([InvoiceValue],FORMAT('Invoice'[InvoiceDate],"MMMM")=SELECTEDVALUE('Calendar'[Date].[Month])),
_column="Month To Date"&&HASONEVALUE('Calendar'[Date].[Month]),CALCULATE([InvoiceValue],Month('Invoice'[InvoiceDate])<=MONTH(MIN('Calendar'[Date])))
)
RETURN _value

Note: This measure only considers the month and does not consider the year as a filter condition.

 

Best Regards,

Wearsky

GalaBI79's avatar
GalaBI79
Regular Visitor
1 year ago

Hi Anonymous ,

 

"habemus papam" 😁

 

Your solution is the right one and it works great, tried it with my tables and the filters finally work correctly.

 

I just have one question, due to my ignorance, I saw that you didn't join the tables with each other. I noticed that in my report, if the invoice table and Calendar are in relation, your filter does not work, but if I remove the relation it works. Why? 🤔