Search results

Oh I see. I misread your image. It's bi-directional relationship. Why bi-directional? Should be one way relationship no? Going to one side to many side. EDIT: Note that simplest way to deal with your issue is to duplicate your contact table and associate one to ContactID and the other to...

Avoid use of many to many relationship in data model. Data model should be optimized for data analysis. This is what is causing your issue. Try splitting or otherwise transforming your table(s) into single fact table and multiple dimension table(s). Star Schema. Using one to many relationships...

By default, user filter is persistent. Announcing Persistent Filters in the Power BI Service | Microsoft Power BI Blog | Microsoft Power BI As for calculation to only include selected item. That's standard behavior of DAX measure, unless you override it with ALL() or other filter context...

Issue isn't with __days alone. But data lineage between __fromDate & __days. You can test this with replacing __fromDate with fixed date. I suspect the issue is with 'Previous Time' table reference in both variables. Try below. [CODE]Quote Count R3M = // get the selected date VAR...

Well... it's 5 year old link. You can find many free samples if you do google search. Yes, you need geo-coordinate to do greater circle distance calculation.

There are number of ways to handle it. 1. Use Text.Split() by comma and check for Text.Length() of List.Last(SplitResult). If = 2 then replace comma with period and vice versa on original string. 2. Use try... otherwise to handle error using implicit conversion to number. If error, perform...

There isn't a way on Desktop app. Desktop app is for authoring and editing. Not for sharing report/visuals.

CROSSFILTER is fine if only one directional calculation is needed. But if you need context transition within your measure. It's best to use CALCULATE with FILTER. It's relatively simple syntax and easier to follow direction change etc. I'm not sure I get your question. I'd recommend uploading...

Will a customer have multiple sales person that's assigned? If not, simple merge to fact table should suffice (filtered to "Y" only). If not, you need to use nested CALCULATE() with FILTER, to take advantage of context transition. Filters CAN Flow Up Hill – Via Formulas That Is - P3 Adaptive

My recommendation is to avoid snowflake (dimension related to dimension) whenever possible and use Star schema or Star Constellation schema (collection of star schema). It will make your downstream DAX calculations that much easier to maintain. Have a read of... Understand star schema and the...

In your code, there is no logic to differentiate between date value for 2021 & 2022. If this is only required for 2021 & 2022. Simple if logic or case statement will do fine. Ex: This should replace code below Dim emptyRow line in your code. Dim dbSheet As Worksheet Set dbSheet =...

I'd recommend uploading file with your data model (date & sales tables). Without knowing structure of your dates table, hard to say what's causing issue with your DAX. I suspect, that you don't have full calendar year (dates) in your dates table. But can't confirm with your sample.

Wait, if you have LAMBDA. Then you have PowerQuery (Data ribbon -> Get & Transform).

Can't think of way to this sort of calculation using formula off top of my head. Though @Peter Bartholomew might be able to show you solution using newer functions added in MS365 (LET, LAMBDA etc). Do you have access to PowerQuery? Or is VBA solution acceptable? FYI - There is one issue with...

Welcome to the forum. As a new member, I'd recommend reading link below and following guideline on getting best results from your posts. Site Rules - New Users - Please Read | Chandoo.org Excel Forums - Become Awesome in Excel Post a sample workbook, with your raw data and manually crafted...

I'd recommend you upload workbook with manually created expected result. Preferably with explanation of your logic. I'm not a mind reader and not sure what your expected result would be from your data.

Just for the hell of it. Single LET version using FILTERXML...

@Peter Bartholomew I just realized, MAP, LAMBDA, LET all made it into Current Release (Version 2204, build 15128.20224). Now, I should start experimenting with them again. I'm hoping VSTACK comes to Current Release soon as well. Using TEXTJOIN and FILTERXML is not ideal to generate union of...

Going to miss your ingenious solutions. Get well soon.

Using DAX. 1. Load table to PQ. Then unpivot value columns. 2. Add following DAX Measure. IRR := VAR date_Max = MAX ( Table1[date] ) VAR neg_Flow = FILTER ( ALL ( Table1 ), Table1[Attribute] = "investment (cashouts)" && Table1[date] <= date_Max ) VAR...

It isn't clear to me what you are trying to calculate here. XIRR calculates IIR for cash flows over schedule which isn't periodic (regular interval). But from your description.... you are trying to calculate IRR at each line. If that is the case... I can't think of good method to accomplish...

Sometimes. All you need is reworking your file from start to find solution. ;) Thanks for sharing your result.

I'd recommend uploading sample of your data (both left and right side tables). It would be ideal if you upload workbook with data model. But if unable to, just two tables clearly indicating which column(s) are used as primary and foreign key. This would make it so much easier for us to help you.

If you are not sure what CALCULATE(Expression, Filter) is doing in my example. Below YouTube video does a good job of explaining it. DAX CALCULATE Trick to Pass Filters from Many to One Side of Relationship - YouTube

If you need it to be more dynamic and also calculate Grand Total. You need to pass filter context using CALCULATE. Ex: UnitBooked := VAR distCount = CALCULATE ( DISTINCTCOUNT ( AppOrders[Crop #] ), SPOrders2022 ) RETURN IF ( distCount >= 1, CALCULATE ( SUM (...